step1-sample-cusip-ciks.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "source": "# CUSIP / CIK Sample Mapping \n",
   "metadata": {
    "collapsed": false
   }
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-06-16T21:08:08.948820Z",
     "start_time": "2024-06-16T21:08:01.856326Z"
    }
   },
   "cell_type": "code",
   "source": [
    "%%capture\n",
    "%pip install neo4j python-dotenv pandas"
   ],
   "outputs": [],
   "execution_count": 3
  },
  {
   "cell_type": "code",
   "source": [
    "import pandas as pd\n",
    "from dotenv import load_dotenv\n",
    "import os\n",
    "\n",
    "pd.set_option('display.width', 0)\n",
    "pd.set_option('display.max_colwidth', 500)\n",
    "pd.set_option('display.max_rows', 50)\n"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-06-16T21:08:14.366972Z",
     "start_time": "2024-06-16T21:08:11.253199Z"
    }
   },
   "outputs": [],
   "execution_count": 4
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Get CUSIP/CIK Map"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "source": [
    "map_df = pd.read_csv('https://raw.githubusercontent.com/leoliu0/cik-cusip-mapping/master/cik-cusip-maps.csv')\n",
    "map_df.cik = map_df.cik.astype(int)\n",
    "map_df.sort_values(by='cusip6')"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-06-16T21:15:17.425550Z",
     "start_time": "2024-06-16T21:15:17.034190Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "           cik  cusip6    cusip8\n",
       "11622  1623590  000001    000001\n",
       "22085  1453593  000001    000001\n",
       "52449  1083672  000001    000001\n",
       "21868  1076966  000001    000001\n",
       "30876  1345016  000001    000001\n",
       "...        ...     ...       ...\n",
       "39963  1099205  Z00107  Z0010713\n",
       "26663  1674447  Z058Z0    Z058Z0\n",
       "15271   880266  Z317FV    Z317FV\n",
       "13757  1050446  Z317FW    Z317FW\n",
       "45897  1077370  Z717FG    Z717FG\n",
       "\n",
       "[56471 rows x 3 columns]"
      ],
      "text/html": [
       "<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>cik</th>\n",
       "      <th>cusip6</th>\n",
       "      <th>cusip8</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>11622</th>\n",
       "      <td>1623590</td>\n",
       "      <td>000001</td>\n",
       "      <td>000001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22085</th>\n",
       "      <td>1453593</td>\n",
       "      <td>000001</td>\n",
       "      <td>000001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52449</th>\n",
       "      <td>1083672</td>\n",
       "      <td>000001</td>\n",
       "      <td>000001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21868</th>\n",
       "      <td>1076966</td>\n",
       "      <td>000001</td>\n",
       "      <td>000001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30876</th>\n",
       "      <td>1345016</td>\n",
       "      <td>000001</td>\n",
       "      <td>000001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39963</th>\n",
       "      <td>1099205</td>\n",
       "      <td>Z00107</td>\n",
       "      <td>Z0010713</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26663</th>\n",
       "      <td>1674447</td>\n",
       "      <td>Z058Z0</td>\n",
       "      <td>Z058Z0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15271</th>\n",
       "      <td>880266</td>\n",
       "      <td>Z317FV</td>\n",
       "      <td>Z317FV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13757</th>\n",
       "      <td>1050446</td>\n",
       "      <td>Z317FW</td>\n",
       "      <td>Z317FW</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45897</th>\n",
       "      <td>1077370</td>\n",
       "      <td>Z717FG</td>\n",
       "      <td>Z717FG</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>56471 rows × 3 columns</p>\n",
       "</div>"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 16
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Get Some Sample SEC Graph Data"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-06-16T21:16:38.438521Z",
     "start_time": "2024-06-16T21:16:34.812892Z"
    }
   },
   "cell_type": "code",
   "source": [
    "from neo4j import GraphDatabase\n",
    "\n",
    "load_dotenv('.env', override=True)\n",
    "NEO4J_URI = os.getenv('OLD_NEO4J_URI')\n",
    "NEO4J_AUTH = (os.getenv('OLD_NEO4J_USERNAME'), os.getenv('OLD_NEO4J_PASSWORD'))\n",
    "\n",
    "with GraphDatabase.driver(NEO4J_URI, auth=NEO4J_AUTH) as driver:\n",
    "    records, summary, keys = driver.execute_query('''MATCH(c:Company) RETURN c.cusip AS cusip, c.companyName AS name''', \n",
    "                         database='neo4j')\n",
    "company_df = pd.DataFrame.from_records([x.data() for x in records])\n",
    "company_df"
   ],
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                  cusip                name\n",
       "0      *W EXP 09/01/202                None\n",
       "1             000000000    Asml Hldg Nv ADR\n",
       "2             00000NANA      NOBLE CORP PLC\n",
       "3             00000NRGV        Energy Vault\n",
       "4             000053673   ASHTEAD GROUP PLC\n",
       "...                 ...                 ...\n",
       "16388         y8162g112   STEPPE CEMENT LTD\n",
       "16389         y8564w103          Teekay Inc\n",
       "16390         y8565n102      Teekay Tankers\n",
       "16391         y8565n300  TEEKAY TANKERS LTD\n",
       "16392         y8897y198       TOP SHIPS INC\n",
       "\n",
       "[16393 rows x 2 columns]"
      ],
      "text/html": [
       "<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>cusip</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>*W EXP 09/01/202</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>000000000</td>\n",
       "      <td>Asml Hldg Nv ADR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>00000NANA</td>\n",
       "      <td>NOBLE CORP PLC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>00000NRGV</td>\n",
       "      <td>Energy Vault</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>000053673</td>\n",
       "      <td>ASHTEAD GROUP PLC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16388</th>\n",
       "      <td>y8162g112</td>\n",
       "      <td>STEPPE CEMENT LTD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16389</th>\n",
       "      <td>y8564w103</td>\n",
       "      <td>Teekay Inc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16390</th>\n",
       "      <td>y8565n102</td>\n",
       "      <td>Teekay Tankers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16391</th>\n",
       "      <td>y8565n300</td>\n",
       "      <td>TEEKAY TANKERS LTD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16392</th>\n",
       "      <td>y8897y198</td>\n",
       "      <td>TOP SHIPS INC</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>16393 rows × 2 columns</p>\n",
       "</div>"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 17
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Format CUSIP 6 and 8\n",
    "Test Merging on Map"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "source": [
    "## Padding of 3 zeros is suspect - likely has a padded zero. This is inconsistent among form13 filers\n",
    "def get_cusip6(cusip):\n",
    "    if cusip.startswith('000'):\n",
    "        return cusip.upper()[1:7]\n",
    "    return cusip.upper().upper()[:6]"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-06-16T21:16:47.029497Z",
     "start_time": "2024-06-16T21:16:47.026372Z"
    }
   },
   "outputs": [],
   "execution_count": 18
  },
  {
   "cell_type": "code",
   "source": [
    "# company_df['cusip6'] = company_df.cusip.str.upper().str[:6]\n",
    "company_df['cusip6'] = company_df.cusip.apply(get_cusip6)\n",
    "# company_df['cusip6_off'] = company_df.cusip.str.upper().str[1:7] # account for some form13 filings which use an extra padded zero\n",
    "company_df['cusip8'] = company_df.cusip.str.upper().str[:8]\n",
    "company_df"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-06-16T21:17:01.907173Z",
     "start_time": "2024-06-16T21:17:01.868208Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                  cusip                name  cusip6    cusip8\n",
       "0      *W EXP 09/01/202                None  *W EXP  *W EXP 0\n",
       "1             000000000    Asml Hldg Nv ADR  000000  00000000\n",
       "2             00000NANA      NOBLE CORP PLC  0000NA  00000NAN\n",
       "3             00000NRGV        Energy Vault  0000NR  00000NRG\n",
       "4             000053673   ASHTEAD GROUP PLC  000536  00005367\n",
       "...                 ...                 ...     ...       ...\n",
       "16388         y8162g112   STEPPE CEMENT LTD  Y8162G  Y8162G11\n",
       "16389         y8564w103          Teekay Inc  Y8564W  Y8564W10\n",
       "16390         y8565n102      Teekay Tankers  Y8565N  Y8565N10\n",
       "16391         y8565n300  TEEKAY TANKERS LTD  Y8565N  Y8565N30\n",
       "16392         y8897y198       TOP SHIPS INC  Y8897Y  Y8897Y19\n",
       "\n",
       "[16393 rows x 4 columns]"
      ],
      "text/html": [
       "<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>cusip</th>\n",
       "      <th>name</th>\n",
       "      <th>cusip6</th>\n",
       "      <th>cusip8</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>*W EXP 09/01/202</td>\n",
       "      <td>None</td>\n",
       "      <td>*W EXP</td>\n",
       "      <td>*W EXP 0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>000000000</td>\n",
       "      <td>Asml Hldg Nv ADR</td>\n",
       "      <td>000000</td>\n",
       "      <td>00000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>00000NANA</td>\n",
       "      <td>NOBLE CORP PLC</td>\n",
       "      <td>0000NA</td>\n",
       "      <td>00000NAN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>00000NRGV</td>\n",
       "      <td>Energy Vault</td>\n",
       "      <td>0000NR</td>\n",
       "      <td>00000NRG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>000053673</td>\n",
       "      <td>ASHTEAD GROUP PLC</td>\n",
       "      <td>000536</td>\n",
       "      <td>00005367</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16388</th>\n",
       "      <td>y8162g112</td>\n",
       "      <td>STEPPE CEMENT LTD</td>\n",
       "      <td>Y8162G</td>\n",
       "      <td>Y8162G11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16389</th>\n",
       "      <td>y8564w103</td>\n",
       "      <td>Teekay Inc</td>\n",
       "      <td>Y8564W</td>\n",
       "      <td>Y8564W10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16390</th>\n",
       "      <td>y8565n102</td>\n",
       "      <td>Teekay Tankers</td>\n",
       "      <td>Y8565N</td>\n",
       "      <td>Y8565N10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16391</th>\n",
       "      <td>y8565n300</td>\n",
       "      <td>TEEKAY TANKERS LTD</td>\n",
       "      <td>Y8565N</td>\n",
       "      <td>Y8565N30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16392</th>\n",
       "      <td>y8897y198</td>\n",
       "      <td>TOP SHIPS INC</td>\n",
       "      <td>Y8897Y</td>\n",
       "      <td>Y8897Y19</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>16393 rows × 4 columns</p>\n",
       "</div>"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 19
  },
  {
   "cell_type": "code",
   "source": [
    "#cusip 8 merge\n",
    "company_df.merge(map_df, on='cusip8', how='inner').groupby(['cusip', 'name','cusip6_x', 'cusip6_y', 'cusip8']).agg({'cik':list})"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-06-16T21:17:14.470094Z",
     "start_time": "2024-06-16T21:17:14.389809Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                                                                             cik\n",
       "cusip     name                     cusip6_x cusip6_y cusip8                     \n",
       "000360206 AAON, Inc.               003602   000360   00036020   [824142, 105634]\n",
       "000361105 AAR CORP                 003611   000361   00036110             [1750]\n",
       "00081T108 ACCO BRANDS CORP         0081T1   00081T   00081T10           [712034]\n",
       "00081t108 ACCO Brands Corp.        0081T1   00081T   00081T10           [712034]\n",
       "000868109 ACNB CORP                008681   000868   00086810           [715579]\n",
       "...                                                                          ...\n",
       "y62267102 Navios Maritime Partners Y62267   Y62267   Y6226710  [1415921, 732857]\n",
       "y7542c130 SCORPIO TANKERS INC      Y7542C   Y7542C   Y7542C13          [1483934]\n",
       "y8564w103 Teekay Inc               Y8564W   Y8564W   Y8564W10  [911971, 1705931]\n",
       "y8565n102 Teekay Tankers           Y8565N   Y8565N   Y8565N10          [1419945]\n",
       "y8565n300 TEEKAY TANKERS LTD       Y8565N   Y8565N   Y8565N30          [1419945]\n",
       "\n",
       "[5551 rows x 1 columns]"
      ],
      "text/html": [
       "<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></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>cik</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>cusip</th>\n",
       "      <th>name</th>\n",
       "      <th>cusip6_x</th>\n",
       "      <th>cusip6_y</th>\n",
       "      <th>cusip8</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>000360206</th>\n",
       "      <th>AAON, Inc.</th>\n",
       "      <th>003602</th>\n",
       "      <th>000360</th>\n",
       "      <th>00036020</th>\n",
       "      <td>[824142, 105634]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>000361105</th>\n",
       "      <th>AAR CORP</th>\n",
       "      <th>003611</th>\n",
       "      <th>000361</th>\n",
       "      <th>00036110</th>\n",
       "      <td>[1750]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>00081T108</th>\n",
       "      <th>ACCO BRANDS CORP</th>\n",
       "      <th>0081T1</th>\n",
       "      <th>00081T</th>\n",
       "      <th>00081T10</th>\n",
       "      <td>[712034]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>00081t108</th>\n",
       "      <th>ACCO Brands Corp.</th>\n",
       "      <th>0081T1</th>\n",
       "      <th>00081T</th>\n",
       "      <th>00081T10</th>\n",
       "      <td>[712034]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>000868109</th>\n",
       "      <th>ACNB CORP</th>\n",
       "      <th>008681</th>\n",
       "      <th>000868</th>\n",
       "      <th>00086810</th>\n",
       "      <td>[715579]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>y62267102</th>\n",
       "      <th>Navios Maritime Partners</th>\n",
       "      <th>Y62267</th>\n",
       "      <th>Y62267</th>\n",
       "      <th>Y6226710</th>\n",
       "      <td>[1415921, 732857]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>y7542c130</th>\n",
       "      <th>SCORPIO TANKERS INC</th>\n",
       "      <th>Y7542C</th>\n",
       "      <th>Y7542C</th>\n",
       "      <th>Y7542C13</th>\n",
       "      <td>[1483934]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>y8564w103</th>\n",
       "      <th>Teekay Inc</th>\n",
       "      <th>Y8564W</th>\n",
       "      <th>Y8564W</th>\n",
       "      <th>Y8564W10</th>\n",
       "      <td>[911971, 1705931]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>y8565n102</th>\n",
       "      <th>Teekay Tankers</th>\n",
       "      <th>Y8565N</th>\n",
       "      <th>Y8565N</th>\n",
       "      <th>Y8565N10</th>\n",
       "      <td>[1419945]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>y8565n300</th>\n",
       "      <th>TEEKAY TANKERS LTD</th>\n",
       "      <th>Y8565N</th>\n",
       "      <th>Y8565N</th>\n",
       "      <th>Y8565N30</th>\n",
       "      <td>[1419945]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5551 rows × 1 columns</p>\n",
       "</div>"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 20
  },
  {
   "cell_type": "code",
   "source": [
    "#cusip 6 merge\n",
    "company_df.merge(map_df, on='cusip6', how='inner').groupby(['cusip', 'name','cusip6']).agg({'cik':list, 'cusip8_x': list, 'cusip8_y': list})"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-06-16T21:17:27.588820Z",
     "start_time": "2024-06-16T21:17:27.322702Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                                                                                                           cik  \\\n",
       "cusip     name                cusip6                                                                             \n",
       "000105510 AFLAC INC           001055                                                                    [4977]   \n",
       "000282410 ABBOTT LABS         002824                                                                    [1800]   \n",
       "001055102 AFLAC INC           001055                                                                    [4977]   \n",
       "001055902 AFLAC INC           001055                                                                    [4977]   \n",
       "001055952 AFLAC INC           001055                                                                    [4977]   \n",
       "...                                                                                                        ...   \n",
       "y7542c130 SCORPIO TANKERS INC Y7542C                                                        [1483934, 1483934]   \n",
       "y8564w103 Teekay Inc          Y8564W                                                 [911971, 1705931, 911971]   \n",
       "y8565n102 Teekay Tankers      Y8565N                                                        [1419945, 1419945]   \n",
       "y8565n300 TEEKAY TANKERS LTD  Y8565N                                                        [1419945, 1419945]   \n",
       "y8897y198 TOP SHIPS INC       Y8897Y  [1296484, 1296484, 1296484, 1296484, 1296484, 1296484, 1296484, 1296484]   \n",
       "\n",
       "                                                                                                              cusip8_x  \\\n",
       "cusip     name                cusip6                                                                                     \n",
       "000105510 AFLAC INC           001055                                                                        [00010551]   \n",
       "000282410 ABBOTT LABS         002824                                                                        [00028241]   \n",
       "001055102 AFLAC INC           001055                                                                        [00105510]   \n",
       "001055902 AFLAC INC           001055                                                                        [00105590]   \n",
       "001055952 AFLAC INC           001055                                                                        [00105595]   \n",
       "...                                                                                                                ...   \n",
       "y7542c130 SCORPIO TANKERS INC Y7542C                                                              [Y7542C13, Y7542C13]   \n",
       "y8564w103 Teekay Inc          Y8564W                                                    [Y8564W10, Y8564W10, Y8564W10]   \n",
       "y8565n102 Teekay Tankers      Y8565N                                                              [Y8565N10, Y8565N10]   \n",
       "y8565n300 TEEKAY TANKERS LTD  Y8565N                                                              [Y8565N30, Y8565N30]   \n",
       "y8897y198 TOP SHIPS INC       Y8897Y  [Y8897Y19, Y8897Y19, Y8897Y19, Y8897Y19, Y8897Y19, Y8897Y19, Y8897Y19, Y8897Y19]   \n",
       "\n",
       "                                                                                                              cusip8_y  \n",
       "cusip     name                cusip6                                                                                    \n",
       "000105510 AFLAC INC           001055                                                                        [00105510]  \n",
       "000282410 ABBOTT LABS         002824                                                                        [00282410]  \n",
       "001055102 AFLAC INC           001055                                                                        [00105510]  \n",
       "001055902 AFLAC INC           001055                                                                        [00105510]  \n",
       "001055952 AFLAC INC           001055                                                                        [00105510]  \n",
       "...                                                                                                                ...  \n",
       "y7542c130 SCORPIO TANKERS INC Y7542C                                                              [Y7542C10, Y7542C13]  \n",
       "y8564w103 Teekay Inc          Y8564W                                                    [Y8564W10, Y8564W10, Y8564W01]  \n",
       "y8565n102 Teekay Tankers      Y8565N                                                              [Y8565N10, Y8565N30]  \n",
       "y8565n300 TEEKAY TANKERS LTD  Y8565N                                                              [Y8565N10, Y8565N30]  \n",
       "y8897y198 TOP SHIPS INC       Y8897Y  [Y8897Y30, Y8897Y12, Y8897Y10, Y8897Y14, Y8897Y16, Y8897Y70, Y8897Y40, Y8897Y18]  \n",
       "\n",
       "[11024 rows x 3 columns]"
      ],
      "text/html": [
       "<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></th>\n",
       "      <th></th>\n",
       "      <th>cik</th>\n",
       "      <th>cusip8_x</th>\n",
       "      <th>cusip8_y</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>cusip</th>\n",
       "      <th>name</th>\n",
       "      <th>cusip6</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>000105510</th>\n",
       "      <th>AFLAC INC</th>\n",
       "      <th>001055</th>\n",
       "      <td>[4977]</td>\n",
       "      <td>[00010551]</td>\n",
       "      <td>[00105510]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>000282410</th>\n",
       "      <th>ABBOTT LABS</th>\n",
       "      <th>002824</th>\n",
       "      <td>[1800]</td>\n",
       "      <td>[00028241]</td>\n",
       "      <td>[00282410]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>001055102</th>\n",
       "      <th>AFLAC INC</th>\n",
       "      <th>001055</th>\n",
       "      <td>[4977]</td>\n",
       "      <td>[00105510]</td>\n",
       "      <td>[00105510]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>001055902</th>\n",
       "      <th>AFLAC INC</th>\n",
       "      <th>001055</th>\n",
       "      <td>[4977]</td>\n",
       "      <td>[00105590]</td>\n",
       "      <td>[00105510]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>001055952</th>\n",
       "      <th>AFLAC INC</th>\n",
       "      <th>001055</th>\n",
       "      <td>[4977]</td>\n",
       "      <td>[00105595]</td>\n",
       "      <td>[00105510]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>y7542c130</th>\n",
       "      <th>SCORPIO TANKERS INC</th>\n",
       "      <th>Y7542C</th>\n",
       "      <td>[1483934, 1483934]</td>\n",
       "      <td>[Y7542C13, Y7542C13]</td>\n",
       "      <td>[Y7542C10, Y7542C13]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>y8564w103</th>\n",
       "      <th>Teekay Inc</th>\n",
       "      <th>Y8564W</th>\n",
       "      <td>[911971, 1705931, 911971]</td>\n",
       "      <td>[Y8564W10, Y8564W10, Y8564W10]</td>\n",
       "      <td>[Y8564W10, Y8564W10, Y8564W01]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>y8565n102</th>\n",
       "      <th>Teekay Tankers</th>\n",
       "      <th>Y8565N</th>\n",
       "      <td>[1419945, 1419945]</td>\n",
       "      <td>[Y8565N10, Y8565N10]</td>\n",
       "      <td>[Y8565N10, Y8565N30]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>y8565n300</th>\n",
       "      <th>TEEKAY TANKERS LTD</th>\n",
       "      <th>Y8565N</th>\n",
       "      <td>[1419945, 1419945]</td>\n",
       "      <td>[Y8565N30, Y8565N30]</td>\n",
       "      <td>[Y8565N10, Y8565N30]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>y8897y198</th>\n",
       "      <th>TOP SHIPS INC</th>\n",
       "      <th>Y8897Y</th>\n",
       "      <td>[1296484, 1296484, 1296484, 1296484, 1296484, 1296484, 1296484, 1296484]</td>\n",
       "      <td>[Y8897Y19, Y8897Y19, Y8897Y19, Y8897Y19, Y8897Y19, Y8897Y19, Y8897Y19, Y8897Y19]</td>\n",
       "      <td>[Y8897Y30, Y8897Y12, Y8897Y10, Y8897Y14, Y8897Y16, Y8897Y70, Y8897Y40, Y8897Y18]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>11024 rows × 3 columns</p>\n",
       "</div>"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 21
  },
  {
   "cell_type": "markdown",
   "source": [
    "# Create Merged and Aggregated Dataset\n",
    "Aggregate to cusip 6: This is what we would use to pull 10k by CIK and map back to cusip"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "source": [
    "merged_df = company_df.drop(columns=['cusip8']).merge(map_df.drop(columns=['cusip8']), on='cusip6', how='inner')\n",
    "merged_df"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-06-16T21:17:34.481136Z",
     "start_time": "2024-06-16T21:17:34.459671Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "           cusip           name  cusip6      cik\n",
       "0      000105510      AFLAC INC  001055     4977\n",
       "1      000282410    ABBOTT LABS  002824     1800\n",
       "2      001055102      AFLAC INC  001055     4977\n",
       "3      001055902      AFLAC INC  001055     4977\n",
       "4      001055952      AFLAC INC  001055     4977\n",
       "...          ...            ...     ...      ...\n",
       "30101  y8897y198  TOP SHIPS INC  Y8897Y  1296484\n",
       "30102  y8897y198  TOP SHIPS INC  Y8897Y  1296484\n",
       "30103  y8897y198  TOP SHIPS INC  Y8897Y  1296484\n",
       "30104  y8897y198  TOP SHIPS INC  Y8897Y  1296484\n",
       "30105  y8897y198  TOP SHIPS INC  Y8897Y  1296484\n",
       "\n",
       "[30106 rows x 4 columns]"
      ],
      "text/html": [
       "<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>cusip</th>\n",
       "      <th>name</th>\n",
       "      <th>cusip6</th>\n",
       "      <th>cik</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>000105510</td>\n",
       "      <td>AFLAC INC</td>\n",
       "      <td>001055</td>\n",
       "      <td>4977</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>000282410</td>\n",
       "      <td>ABBOTT LABS</td>\n",
       "      <td>002824</td>\n",
       "      <td>1800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>001055102</td>\n",
       "      <td>AFLAC INC</td>\n",
       "      <td>001055</td>\n",
       "      <td>4977</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>001055902</td>\n",
       "      <td>AFLAC INC</td>\n",
       "      <td>001055</td>\n",
       "      <td>4977</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>001055952</td>\n",
       "      <td>AFLAC INC</td>\n",
       "      <td>001055</td>\n",
       "      <td>4977</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30101</th>\n",
       "      <td>y8897y198</td>\n",
       "      <td>TOP SHIPS INC</td>\n",
       "      <td>Y8897Y</td>\n",
       "      <td>1296484</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30102</th>\n",
       "      <td>y8897y198</td>\n",
       "      <td>TOP SHIPS INC</td>\n",
       "      <td>Y8897Y</td>\n",
       "      <td>1296484</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30103</th>\n",
       "      <td>y8897y198</td>\n",
       "      <td>TOP SHIPS INC</td>\n",
       "      <td>Y8897Y</td>\n",
       "      <td>1296484</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30104</th>\n",
       "      <td>y8897y198</td>\n",
       "      <td>TOP SHIPS INC</td>\n",
       "      <td>Y8897Y</td>\n",
       "      <td>1296484</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30105</th>\n",
       "      <td>y8897y198</td>\n",
       "      <td>TOP SHIPS INC</td>\n",
       "      <td>Y8897Y</td>\n",
       "      <td>1296484</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>30106 rows × 4 columns</p>\n",
       "</div>"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 22
  },
  {
   "cell_type": "code",
   "source": [
    "def most_frequent(s):\n",
    "    x = s.tolist()\n",
    "    return max(set(x), key=x.count)\n",
    "\n",
    "cusip_cik_df = merged_df.groupby(['cusip6']).agg({'cik':most_frequent, 'name':set, 'cusip':set}).reset_index()\n",
    "cusip_cik_df"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-06-16T21:17:40.792616Z",
     "start_time": "2024-06-16T21:17:40.702462Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "      cusip6      cik  \\\n",
       "0     001055     4977   \n",
       "1     001084  1100441   \n",
       "2     001228  1514281   \n",
       "3     00123Q  1423689   \n",
       "4     00130H   874761   \n",
       "...      ...      ...   \n",
       "4643  Y81669  1328919   \n",
       "4644  Y8564W   911971   \n",
       "4645  Y8565N  1419945   \n",
       "4646  Y8897Y  1296484   \n",
       "4647  Y95308  1631574   \n",
       "\n",
       "                                                                  name  \\\n",
       "0                                                          {AFLAC INC}   \n",
       "1                                                          {AGCO CORP}   \n",
       "2                                       {AG MORTGAGE INVESTMENT TRUST}   \n",
       "3                              {AGNC INVT CORP, AGNC Investment Corp.}   \n",
       "4                                                           {AES CORP}   \n",
       "...                                                                ...   \n",
       "4643                                                  {STEALTHGAS INC}   \n",
       "4644                                  {TEEKAY CORPORATION, Teekay Inc}   \n",
       "4645  {TEEKAY TANKERS LTD, Teekay Tankers, TEEKAY TANKERS LTD-CLASS A}   \n",
       "4646                                                   {TOP SHIPS INC}   \n",
       "4647                                          {WAVE LIFE SCIENCES LTD}   \n",
       "\n",
       "                                                        cusip  \n",
       "0                {001055102, 001055952, 000105510, 001055902}  \n",
       "1                           {001084952, 001084902, 001084102}  \n",
       "2                                                 {001228501}  \n",
       "3                {00123q104, 00123Q104, 00123Q954, 00123Q904}  \n",
       "4                {00130H905, 00130H955, 00130H105, 00130h105}  \n",
       "...                                                       ...  \n",
       "4643                                              {Y81669106}  \n",
       "4644             {Y8564W103, y8564w103, Y8564W903, Y8564W953}  \n",
       "4645  {Y8565N900, y8565n102, Y8565N300, y8565n300, Y8565N950}  \n",
       "4646                                              {y8897y198}  \n",
       "4647                                              {Y95308105}  \n",
       "\n",
       "[4648 rows x 4 columns]"
      ],
      "text/html": [
       "<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>cusip6</th>\n",
       "      <th>cik</th>\n",
       "      <th>name</th>\n",
       "      <th>cusip</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>001055</td>\n",
       "      <td>4977</td>\n",
       "      <td>{AFLAC INC}</td>\n",
       "      <td>{001055102, 001055952, 000105510, 001055902}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>001084</td>\n",
       "      <td>1100441</td>\n",
       "      <td>{AGCO CORP}</td>\n",
       "      <td>{001084952, 001084902, 001084102}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>001228</td>\n",
       "      <td>1514281</td>\n",
       "      <td>{AG MORTGAGE INVESTMENT TRUST}</td>\n",
       "      <td>{001228501}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>00123Q</td>\n",
       "      <td>1423689</td>\n",
       "      <td>{AGNC INVT CORP, AGNC Investment Corp.}</td>\n",
       "      <td>{00123q104, 00123Q104, 00123Q954, 00123Q904}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>00130H</td>\n",
       "      <td>874761</td>\n",
       "      <td>{AES CORP}</td>\n",
       "      <td>{00130H905, 00130H955, 00130H105, 00130h105}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4643</th>\n",
       "      <td>Y81669</td>\n",
       "      <td>1328919</td>\n",
       "      <td>{STEALTHGAS INC}</td>\n",
       "      <td>{Y81669106}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4644</th>\n",
       "      <td>Y8564W</td>\n",
       "      <td>911971</td>\n",
       "      <td>{TEEKAY CORPORATION, Teekay Inc}</td>\n",
       "      <td>{Y8564W103, y8564w103, Y8564W903, Y8564W953}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4645</th>\n",
       "      <td>Y8565N</td>\n",
       "      <td>1419945</td>\n",
       "      <td>{TEEKAY TANKERS LTD, Teekay Tankers, TEEKAY TANKERS LTD-CLASS A}</td>\n",
       "      <td>{Y8565N900, y8565n102, Y8565N300, y8565n300, Y8565N950}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4646</th>\n",
       "      <td>Y8897Y</td>\n",
       "      <td>1296484</td>\n",
       "      <td>{TOP SHIPS INC}</td>\n",
       "      <td>{y8897y198}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4647</th>\n",
       "      <td>Y95308</td>\n",
       "      <td>1631574</td>\n",
       "      <td>{WAVE LIFE SCIENCES LTD}</td>\n",
       "      <td>{Y95308105}</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4648 rows × 4 columns</p>\n",
       "</div>"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 23
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "## Write Sample Mapping"
  },
  {
   "cell_type": "code",
   "source": "cusip_cik_df.to_csv('cik-cusip-sample-map.csv', index=False)",
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-06-16T21:18:35.627020Z",
     "start_time": "2024-06-16T21:18:35.590433Z"
    }
   },
   "outputs": [],
   "execution_count": 24
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}