Fuzzy_match_of_public_companies.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Fuzzy_match_of_public\n",
    "- Wang Jin & Zhongjie He\n",
    "\n",
    "#### Problem describtion\n",
    "The aim of this project is to pick the public companies(have listed on American stock exchanges) from the original 222,450 companies(some are repeated). The file **work1.csv** is the work file and the **work2** is the public companies(more than 6,000) in United States' share exchanges. Mainly we apply *fuzzy wuzzy* algorithm and picked the required ones. https://github.com/seatgeek/fuzzywuzzy (developped by SeatGeet)\n",
    "\n",
    "**There are two main difficulties. **\n",
    "- The first names in the file with unchecked companies are not very formal, say **'Apple company '** instead of **'Apple Inc'**. We designed the *fuzzy wuzzy* value by making experiments manually in advance. \n",
    "- The second is massive data leads to much running time (it is estimated more than **12 hours** for the first version programe). We reduced it to **less than five minutes** by devide the matching procedure into two steps after carefully observed the data structure.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "D:\\Users\\jinwa\\Anaconda3\\lib\\site-packages\\fuzzywuzzy\\fuzz.py:35: UserWarning: Using slow pure-python SequenceMatcher. Install python-Levenshtein to remove this warning\n",
      "  warnings.warn('Using slow pure-python SequenceMatcher. Install python-Levenshtein to remove this warning')\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "from fuzzywuzzy import fuzz\n",
    "from fuzzywuzzy import process\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')\n",
    "# fuzz.token_set_ratio(\"Advanced Semiconductor Engineering, Inc.\", \"Advance Pain Centers\"    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>coname</th>\n",
       "      <th>money</th>\n",
       "      <th>year</th>\n",
       "      <th>comma</th>\n",
       "      <th>haha</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>222450</td>\n",
       "      <td>222450</td>\n",
       "      <td>222450</td>\n",
       "      <td>222447</td>\n",
       "      <td>222447</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unique</th>\n",
       "      <td>41497</td>\n",
       "      <td>12710</td>\n",
       "      <td>39</td>\n",
       "      <td>1</td>\n",
       "      <td>537</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>top</th>\n",
       "      <td>Blue Cross/Blue Shield</td>\n",
       "      <td>,0,</td>\n",
       "      <td>2009</td>\n",
       "      <td>,</td>\n",
       "      <td>Y4000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>freq</th>\n",
       "      <td>449</td>\n",
       "      <td>49595</td>\n",
       "      <td>9547</td>\n",
       "      <td>222447</td>\n",
       "      <td>27719</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        coname   money    year   comma    haha\n",
       "count                   222450  222450  222450  222447  222447\n",
       "unique                   41497   12710      39       1     537\n",
       "top     Blue Cross/Blue Shield     ,0,    2009       ,   Y4000\n",
       "freq                       449   49595    9547  222447   27719"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#1.import company data we want search\n",
    "co = pd.read_csv(\"work1.csv\", sep=',')\n",
    "# co.head()\n",
    "co.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>sharename</th>\n",
       "      <th>sharecode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1-800 FLOWERS.COM, Inc.</td>\n",
       "      <td>FLWS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1347 Property Insurance Holdings, Inc.</td>\n",
       "      <td>PIH</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>180 Degree Capital Corp.</td>\n",
       "      <td>TURN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1st Constitution Bancorp (NJ)</td>\n",
       "      <td>FCCY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1st Source Corporation</td>\n",
       "      <td>SRCE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>21Vianet Group, Inc.</td>\n",
       "      <td>VNET</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>22nd Century Group, Inc</td>\n",
       "      <td>XXII</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2U, Inc.</td>\n",
       "      <td>TWOU</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>3D Systems Corporation</td>\n",
       "      <td>DDD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>3M Company</td>\n",
       "      <td>MMM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>500.com Limited</td>\n",
       "      <td>WBAI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>51job, Inc.</td>\n",
       "      <td>JOBS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>58.com Inc.</td>\n",
       "      <td>WUBA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>8point3 Energy Partners LP</td>\n",
       "      <td>CAFD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>8x8 Inc</td>\n",
       "      <td>EGHT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>A V Homes, Inc.</td>\n",
       "      <td>AVHI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>A-Mark Precious Metals, Inc.</td>\n",
       "      <td>AMRK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>A. Schulman, Inc.</td>\n",
       "      <td>SHLM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>A.H. Belo Corporation</td>\n",
       "      <td>AHC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>A10 Networks, Inc.</td>\n",
       "      <td>ATEN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>AAC Holdings, Inc.</td>\n",
       "      <td>AAC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>AAON, Inc.</td>\n",
       "      <td>AAON</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>AAR Corp.</td>\n",
       "      <td>AIR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>ABAXIS, Inc.</td>\n",
       "      <td>ABAX</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>ABB Ltd</td>\n",
       "      <td>ABB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>ABIOMED, Inc.</td>\n",
       "      <td>ABMD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>ABM Industries Incorporated</td>\n",
       "      <td>ABM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>AC Immune SA</td>\n",
       "      <td>ACIU</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>ACADIA Pharmaceuticals Inc.</td>\n",
       "      <td>ACAD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>ACI Worldwide, Inc.</td>\n",
       "      <td>ACIW</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6727</th>\n",
       "      <td>iShares S&amp;P Global Clean Energy Index Fund</td>\n",
       "      <td>ICLN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6728</th>\n",
       "      <td>iShares S&amp;P Global Timber &amp; Forestry Index Fund</td>\n",
       "      <td>WOOD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6729</th>\n",
       "      <td>iShares S&amp;P India Nifty 50 Index Fund</td>\n",
       "      <td>INDY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6730</th>\n",
       "      <td>iShares S&amp;P Small-Cap 600 Growth ETF</td>\n",
       "      <td>IJT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6731</th>\n",
       "      <td>iShares Select Dividend ETF</td>\n",
       "      <td>DVY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6732</th>\n",
       "      <td>iShares Short Treasury Bond ETF</td>\n",
       "      <td>SHV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6733</th>\n",
       "      <td>iShares U.S. Credit Bond ETF</td>\n",
       "      <td>CRED</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6734</th>\n",
       "      <td>iShares U.S. Preferred Stock ETF</td>\n",
       "      <td>PFF</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6735</th>\n",
       "      <td>iShares iBoxx $ High Yield ex Oil &amp; Gas Corpor...</td>\n",
       "      <td>HYXE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6736</th>\n",
       "      <td>iStar Financial Inc.</td>\n",
       "      <td>STAR^I</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6737</th>\n",
       "      <td>iStar Financial Inc.</td>\n",
       "      <td>STAR^G</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6738</th>\n",
       "      <td>iStar Financial Inc.</td>\n",
       "      <td>STAR^F</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6739</th>\n",
       "      <td>iStar Financial Inc.</td>\n",
       "      <td>STAR^E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6740</th>\n",
       "      <td>iStar Financial Inc.</td>\n",
       "      <td>STAR^D</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6741</th>\n",
       "      <td>iStar Financial Inc.</td>\n",
       "      <td>STAR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6742</th>\n",
       "      <td>icad inc.</td>\n",
       "      <td>ICAD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6743</th>\n",
       "      <td>inTest Corporation</td>\n",
       "      <td>INTT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6744</th>\n",
       "      <td>j2 Global, Inc.</td>\n",
       "      <td>JCOM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6745</th>\n",
       "      <td>lululemon athletica inc.</td>\n",
       "      <td>LULU</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6746</th>\n",
       "      <td>magicJack VocalTec Ltd</td>\n",
       "      <td>CALL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6747</th>\n",
       "      <td>pSivida Corp.</td>\n",
       "      <td>PSDV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6748</th>\n",
       "      <td>pdvWireless, Inc.</td>\n",
       "      <td>PDVW</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6749</th>\n",
       "      <td>rEVO Biologics, Inc.</td>\n",
       "      <td>RBIO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6750</th>\n",
       "      <td>root9B Holdings, Inc.</td>\n",
       "      <td>RTNB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6751</th>\n",
       "      <td>support.com, Inc.</td>\n",
       "      <td>SPRT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6752</th>\n",
       "      <td>trivago N.V.</td>\n",
       "      <td>TRVG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6753</th>\n",
       "      <td>tronc, Inc.</td>\n",
       "      <td>TRNC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6754</th>\n",
       "      <td>uniQure N.V.</td>\n",
       "      <td>QURE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6755</th>\n",
       "      <td>vTv Therapeutics Inc.</td>\n",
       "      <td>VTVT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6756</th>\n",
       "      <td>voxeljet AG</td>\n",
       "      <td>VJET</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6757 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                              sharename       sharecode\n",
       "0                               1-800 FLOWERS.COM, Inc.            FLWS\n",
       "1                1347 Property Insurance Holdings, Inc.             PIH\n",
       "2                              180 Degree Capital Corp.            TURN\n",
       "3                         1st Constitution Bancorp (NJ)            FCCY\n",
       "4                                1st Source Corporation            SRCE\n",
       "5                                  21Vianet Group, Inc.            VNET\n",
       "6                               22nd Century Group, Inc            XXII\n",
       "7                                              2U, Inc.            TWOU\n",
       "8                                3D Systems Corporation             DDD\n",
       "9                                            3M Company             MMM\n",
       "10                                      500.com Limited            WBAI\n",
       "11                                          51job, Inc.            JOBS\n",
       "12                                          58.com Inc.            WUBA\n",
       "13                           8point3 Energy Partners LP            CAFD\n",
       "14                                              8x8 Inc            EGHT\n",
       "15                                      A V Homes, Inc.            AVHI\n",
       "16                         A-Mark Precious Metals, Inc.            AMRK\n",
       "17                                    A. Schulman, Inc.            SHLM\n",
       "18                                A.H. Belo Corporation             AHC\n",
       "19                                   A10 Networks, Inc.            ATEN\n",
       "20                                   AAC Holdings, Inc.             AAC\n",
       "21                                           AAON, Inc.            AAON\n",
       "22                                            AAR Corp.             AIR\n",
       "23                                         ABAXIS, Inc.            ABAX\n",
       "24                                              ABB Ltd             ABB\n",
       "25                                        ABIOMED, Inc.            ABMD\n",
       "26                          ABM Industries Incorporated             ABM\n",
       "27                                         AC Immune SA            ACIU\n",
       "28                          ACADIA Pharmaceuticals Inc.            ACAD\n",
       "29                                  ACI Worldwide, Inc.            ACIW\n",
       "...                                                 ...             ...\n",
       "6727         iShares S&P Global Clean Energy Index Fund            ICLN\n",
       "6728    iShares S&P Global Timber & Forestry Index Fund            WOOD\n",
       "6729              iShares S&P India Nifty 50 Index Fund            INDY\n",
       "6730               iShares S&P Small-Cap 600 Growth ETF             IJT\n",
       "6731                        iShares Select Dividend ETF             DVY\n",
       "6732                    iShares Short Treasury Bond ETF             SHV\n",
       "6733                       iShares U.S. Credit Bond ETF            CRED\n",
       "6734                   iShares U.S. Preferred Stock ETF             PFF\n",
       "6735  iShares iBoxx $ High Yield ex Oil & Gas Corpor...            HYXE\n",
       "6736                               iStar Financial Inc.          STAR^I\n",
       "6737                               iStar Financial Inc.          STAR^G\n",
       "6738                               iStar Financial Inc.          STAR^F\n",
       "6739                               iStar Financial Inc.          STAR^E\n",
       "6740                               iStar Financial Inc.          STAR^D\n",
       "6741                               iStar Financial Inc.  STAR          \n",
       "6742                                          icad inc.            ICAD\n",
       "6743                                 inTest Corporation            INTT\n",
       "6744                                    j2 Global, Inc.            JCOM\n",
       "6745                           lululemon athletica inc.            LULU\n",
       "6746                             magicJack VocalTec Ltd            CALL\n",
       "6747                                      pSivida Corp.            PSDV\n",
       "6748                                  pdvWireless, Inc.            PDVW\n",
       "6749                               rEVO Biologics, Inc.            RBIO\n",
       "6750                             root9B Holdings, Inc.             RTNB\n",
       "6751                                  support.com, Inc.            SPRT\n",
       "6752                                       trivago N.V.            TRVG\n",
       "6753                                        tronc, Inc.            TRNC\n",
       "6754                                       uniQure N.V.            QURE\n",
       "6755                              vTv Therapeutics Inc.            VTVT\n",
       "6756                                        voxeljet AG            VJET\n",
       "\n",
       "[6757 rows x 2 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#import public companies data\n",
    "share = pd.read_csv(\"work2.csv\")\n",
    "share.sort_values('sharename', inplace=True)\n",
    "share.reset_index(inplace=True)\n",
    "share.drop('index', axis=1, inplace=True)\n",
    "share"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>coname</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1-800 Contacts</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1-800-DoNotCall</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1-800-Doctors</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1-800-Flowers.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1-800-Medivan</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              coname\n",
       "0     1-800 Contacts\n",
       "1    1-800-DoNotCall\n",
       "2      1-800-Doctors\n",
       "3  1-800-Flowers.com\n",
       "4      1-800-Medivan"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#2.make company name unique\n",
    "co_name = co[['coname']]\n",
    "co_name.drop_duplicates(inplace=True)\n",
    "co_name.sort_values('coname', inplace=True)\n",
    "co_name.reset_index(inplace=True)\n",
    "co_name.drop(['index'], axis=1, inplace=True)\n",
    "co_name.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>coname</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>41497</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unique</th>\n",
       "      <td>41497</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>top</th>\n",
       "      <td>National Milk Producers Federation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>freq</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                    coname\n",
       "count                                41497\n",
       "unique                               41497\n",
       "top     National Milk Producers Federation\n",
       "freq                                     1"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "co_name.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0 3\n",
      "7 60\n",
      "8 78\n",
      "14 129\n",
      "19 153\n",
      "22 161\n",
      "22 164\n",
      "25 177\n",
      "26 178\n",
      "87 182\n",
      "117 190\n",
      "1.0%...\n",
      "31 220\n",
      "149 221\n",
      "145 223\n",
      "31 225\n",
      "33 226\n",
      "149 230\n",
      "139 232\n",
      "35 237\n",
      "35 238\n",
      "158 239\n",
      "44 300\n",
      "240 312\n",
      "262 319\n",
      "49 324\n",
      "49 326\n",
      "354 328\n",
      "313 329\n",
      "53 335\n",
      "48 338\n",
      "53 341\n",
      "58 354\n",
      "418 361\n",
      "418 362\n",
      "442 371\n",
      "61 389\n",
      "476 391\n",
      "65 396\n",
      "65 397\n",
      "66 401\n",
      "521 406\n",
      "69 425\n",
      "69 427\n",
      "70 429\n",
      "621 463\n",
      "2.0%...\n",
      "3.0%...\n",
      "4.0%...\n",
      "5.0%...\n",
      "6.0%...\n",
      "7.0%...\n",
      "8.0%...\n",
      "9.0%...\n",
      "10.0%...\n",
      "11.0%...\n",
      "687 4399\n",
      "762 4406\n",
      "651 4417\n",
      "651 4418\n",
      "652 4427\n",
      "656 4447\n",
      "658 4452\n",
      "668 4473\n",
      "675 4500\n",
      "675 4501\n",
      "12.0%...\n",
      "13.0%...\n",
      "14.0%...\n",
      "15.0%...\n",
      "1029 6197\n",
      "1029 6203\n",
      "1032 6211\n",
      "1029 6213\n",
      "1029 6216\n",
      "1173 6218\n",
      "1204 6222\n",
      "1046 6229\n",
      "1046 6234\n",
      "1267 6272\n",
      "1053 6281\n",
      "1058 6282\n",
      "1060 6290\n",
      "1060 6292\n",
      "1303 6303\n",
      "1062 6312\n",
      "1069 6324\n",
      "1072 6341\n",
      "1072 6342\n",
      "1073 6347\n",
      "1075 6353\n",
      "1077 6355\n",
      "1075 6356\n",
      "1075 6357\n",
      "1078 6358\n",
      "1479 6365\n",
      "1615 6370\n",
      "1082 6376\n",
      "1083 6390\n",
      "1083 6393\n",
      "1090 6402\n",
      "1090 6403\n",
      "1091 6405\n",
      "1657 6407\n",
      "1094 6415\n",
      "16.0%...\n",
      "1106 6448\n",
      "1109 6449\n",
      "17.0%...\n",
      "18.0%...\n",
      "19.0%...\n",
      "20.0%...\n",
      "21.0%...\n",
      "22.0%...\n",
      "23.0%...\n",
      "24.0%...\n",
      "25.0%...\n",
      "26.0%...\n",
      "27.0%...\n",
      "28.0%...\n",
      "1724 11594\n",
      "1725 11613\n",
      "1854 11633\n",
      "1734 11639\n",
      "1741 11682\n",
      "1741 11686\n",
      "1742 11690\n",
      "29.0%...\n",
      "30.0%...\n",
      "31.0%...\n",
      "1925 12819\n",
      "2034 12855\n",
      "1932 12917\n",
      "2117 12934\n",
      "1941 12939\n",
      "2175 12951\n",
      "2182 12965\n",
      "32.0%...\n",
      "33.0%...\n",
      "34.0%...\n",
      "35.0%...\n",
      "2232 14341\n",
      "2233 14347\n",
      "2345 14371\n",
      "2378 14372\n",
      "2237 14374\n",
      "2244 14376\n",
      "36.0%...\n",
      "37.0%...\n",
      "38.0%...\n",
      "2575 15789\n",
      "2685 15819\n",
      "2593 15883\n",
      "2600 15895\n",
      "39.0%...\n",
      "40.0%...\n",
      "41.0%...\n",
      "2865 17202\n",
      "42.0%...\n",
      "2868 17229\n",
      "2868 17230\n",
      "2873 17247\n",
      "2873 17248\n",
      "2876 17267\n",
      "2878 17276\n",
      "2880 17288\n",
      "2884 17291\n",
      "2885 17294\n",
      "43.0%...\n",
      "44.0%...\n",
      "45.0%...\n",
      "3064 18612\n",
      "6658 18624\n",
      "6658 18625\n",
      "3070 18630\n",
      "3070 18631\n",
      "3070 18635\n",
      "3070 18640\n",
      "3070 18642\n",
      "3070 18647\n",
      "3073 18648\n",
      "3074 18656\n",
      "3076 18660\n",
      "3126 18697\n",
      "3085 18710\n",
      "3131 18716\n",
      "3089 18718\n",
      "3261 18722\n",
      "6661 18736\n",
      "6661 18740\n",
      "6693 18754\n",
      "6693 18755\n",
      "3098 18762\n",
      "3275 18776\n",
      "46.0%...\n",
      "47.0%...\n",
      "48.0%...\n",
      "49.0%...\n",
      "6744 20300\n",
      "50.0%...\n",
      "3364 20810\n",
      "3391 20817\n",
      "3368 20820\n",
      "3373 20822\n",
      "3378 20843\n",
      "3384 20845\n",
      "3386 20856\n",
      "51.0%...\n",
      "52.0%...\n",
      "3483 21491\n",
      "3530 21515\n",
      "3507 21516\n",
      "3488 21525\n",
      "3553 21538\n",
      "3574 21539\n",
      "3495 21551\n",
      "3638 21557\n",
      "3504 21585\n",
      "53.0%...\n",
      "54.0%...\n",
      "55.0%...\n",
      "3673 22789\n",
      "3680 22806\n",
      "3685 22822\n",
      "3799 22837\n",
      "3766 22839\n",
      "3692 22850\n",
      "3692 22853\n",
      "3692 22854\n",
      "3695 22880\n",
      "3696 22886\n",
      "3696 22898\n",
      "3714 22908\n",
      "3712 22910\n",
      "3714 22914\n",
      "3715 22915\n",
      "3939 22921\n",
      "3720 22935\n",
      "3730 22974\n",
      "3736 22987\n",
      "56.0%...\n",
      "57.0%...\n",
      "58.0%...\n",
      "59.0%...\n",
      "60.0%...\n",
      "61.0%...\n",
      "4115 25339\n",
      "4029 25341\n",
      "4115 25342\n",
      "4031 25360\n",
      "4043 25382\n",
      "4043 25384\n",
      "4172 25401\n",
      "4158 25409\n",
      "4048 25423\n",
      "4049 25424\n",
      "4053 25437\n",
      "4056 25452\n",
      "4057 25458\n",
      "4064 25479\n",
      "4065 25480\n",
      "4066 25483\n",
      "4066 25484\n",
      "62.0%...\n",
      "63.0%...\n",
      "64.0%...\n",
      "65.0%...\n",
      "66.0%...\n",
      "67.0%...\n",
      "68.0%...\n",
      "4380 28140\n",
      "4385 28141\n",
      "4441 28150\n",
      "4392 28156\n",
      "4463 28159\n",
      "4493 28164\n",
      "4490 28166\n",
      "4394 28174\n",
      "69.0%...\n",
      "70.0%...\n",
      "4526 29001\n",
      "4528 29008\n",
      "4691 29025\n",
      "4673 29028\n",
      "4539 29043\n",
      "4540 29044\n",
      "4750 29050\n",
      "4558 29076\n",
      "4562 29079\n",
      "4563 29081\n",
      "4779 29082\n",
      "4564 29088\n",
      "4861 29097\n",
      "4581 29112\n",
      "4582 29113\n",
      "4581 29115\n",
      "4930 29119\n",
      "4583 29123\n",
      "71.0%...\n",
      "72.0%...\n",
      "73.0%...\n",
      "74.0%...\n",
      "75.0%...\n",
      "4943 31269\n",
      "4946 31272\n",
      "76.0%...\n",
      "4986 31451\n",
      "5087 31474\n",
      "5064 31478\n",
      "5087 31482\n",
      "5052 31484\n",
      "5004 31495\n",
      "5007 31508\n",
      "5015 31529\n",
      "5016 31530\n",
      "77.0%...\n",
      "78.0%...\n",
      "79.0%...\n",
      "5193 32805\n",
      "5251 32822\n",
      "5260 32829\n",
      "5197 32831\n",
      "5258 32833\n",
      "5200 32836\n",
      "5202 32852\n",
      "5203 32865\n",
      "5206 32887\n",
      "5389 32896\n",
      "5411 32900\n",
      "5407 32901\n",
      "5407 32909\n",
      "5211 32912\n",
      "5213 32915\n",
      "5214 32916\n",
      "5215 32921\n",
      "5220 32929\n",
      "5447 32930\n",
      "5220 32936\n",
      "5453 32940\n",
      "5471 32948\n",
      "5467 32949\n",
      "5230 32966\n",
      "5606 32984\n",
      "5236 32990\n",
      "80.0%...\n",
      "5557 32991\n",
      "5605 32994\n",
      "5552 32996\n",
      "5241 33005\n",
      "5670 33015\n",
      "5675 33016\n",
      "81.0%...\n",
      "82.0%...\n",
      "83.0%...\n",
      "84.0%...\n",
      "85.0%...\n",
      "86.0%...\n",
      "87.0%...\n",
      "88.0%...\n",
      "5696 36316\n",
      "5696 36317\n",
      "5699 36320\n",
      "5703 36334\n",
      "5711 36341\n",
      "5713 36347\n",
      "5714 36349\n",
      "5729 36379\n",
      "5966 36406\n",
      "5744 36434\n",
      "5744 36436\n",
      "89.0%...\n",
      "90.0%...\n",
      "91.0%...\n",
      "92.0%...\n",
      "6075 38103\n",
      "6079 38120\n",
      "6107 38125\n",
      "6094 38208\n",
      "6085 38221\n",
      "6085 38222\n",
      "6087 38240\n",
      "6085 38263\n",
      "6087 38272\n",
      "6087 38277\n",
      "93.0%...\n",
      "6088 38409\n",
      "94.0%...\n",
      "6233 39042\n",
      "6280 39060\n",
      "6182 39063\n",
      "95.0%...\n",
      "96.0%...\n",
      "6373 39811\n",
      "6458 39813\n",
      "6524 39829\n",
      "6381 39854\n",
      "97.0%...\n",
      "98.0%...\n",
      "99.0%...\n",
      "6572 41057\n",
      "6573 41058\n",
      "6593 41138\n",
      "6610 41140\n",
      "100.0%...\n",
      "235.5105848312378\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "{'1-800-Flowers.com': 'FLWS',\n",
       " '2U Inc': 'TWOU',\n",
       " '3D Systems': 'DDD',\n",
       " '8X8 Inc': 'EGHT',\n",
       " 'A10 Networks Government': 'ATEN',\n",
       " 'AAI Corp': 'AIR',\n",
       " 'AAR Corp': 'AIR',\n",
       " 'ABIOMED Inc': 'ABMD',\n",
       " 'ABM Industries': 'ABM',\n",
       " 'ABS Global Inc': 'FCO',\n",
       " 'ACA International': 'ATV',\n",
       " 'ADA Technologies': 'AEY',\n",
       " 'ADA-ES Inc': 'ADXSW',\n",
       " 'ADCOR Industries': 'AEIS',\n",
       " 'ADI Technologies': 'AEY',\n",
       " 'ADOMANI Inc': 'ADOM',\n",
       " 'ADS Inc': 'ADXSW',\n",
       " 'ADSI Inc': 'ASIX',\n",
       " 'AECOM Global': 'ACM',\n",
       " 'AECOM Technology Corp': 'ACM',\n",
       " 'AEGON NV': 'AEB',\n",
       " 'AK Steel': 'AKS',\n",
       " 'ALLETE Inc': 'ALE',\n",
       " 'ALT Solutions': 'MDRX',\n",
       " 'AM Networks Inc': 'AMCX',\n",
       " 'AMC Networks': 'AMCX',\n",
       " 'AMD Industries': 'ARII',\n",
       " 'AMEC': 'AMFW',\n",
       " 'AMG Healthcare Services': 'AMN',\n",
       " 'AMI Entertainment': 'AMC',\n",
       " 'AMN Healthcare Services': 'AMN',\n",
       " 'ANSAR Inc': 'ANSS',\n",
       " 'AON PLC': 'AON',\n",
       " 'AON plc': 'AON',\n",
       " 'API Technologies': 'AGTC',\n",
       " 'ARCA Biopharma': 'ABIO',\n",
       " 'ARCCA Inc': 'ARCH',\n",
       " 'ARGUS INTERNATIONAL': 'ARRS',\n",
       " 'ARMA International': 'ARRS',\n",
       " 'ASA': 'ASA',\n",
       " 'ASCENT': 'ASCMA',\n",
       " 'AT&T': 'T',\n",
       " 'AT&T Inc': 'T',\n",
       " 'ATA': 'ATAI',\n",
       " 'AWARE': 'AWRE',\n",
       " 'BAE Systems': 'BLDP',\n",
       " 'BAST Services': 'BBSI',\n",
       " 'BCO Inc': 'BCE',\n",
       " 'BCS Inc': 'BCE',\n",
       " 'BEST Inc': 'BSTI',\n",
       " 'BHP Billiton': 'BHP',\n",
       " 'BIFMA International': 'BKYI',\n",
       " 'BP': 'BPT',\n",
       " 'BW Technologies': 'BWXT',\n",
       " 'BWX Technologies': 'BWXT',\n",
       " 'CA': 'CA',\n",
       " 'CA Inc': 'CA',\n",
       " 'CACI International': 'CACI',\n",
       " 'CAE Inc': 'CA',\n",
       " 'CAMP Inc': 'CA',\n",
       " 'CAN Capital': 'CBF',\n",
       " 'CARE': 'CRCM',\n",
       " 'CBE Group': 'CBG',\n",
       " 'CBRE Group': 'CBG',\n",
       " 'CEMEX Inc': 'CETX',\n",
       " 'CES Environmental': 'CECE',\n",
       " 'CF Industries': 'CF',\n",
       " 'CG Corr Group': 'GIB',\n",
       " 'CGI Group': 'GIB',\n",
       " 'CHF International': 'CRL',\n",
       " 'CHS Inc': 'CHSCL',\n",
       " 'CIT Group': 'CIT',\n",
       " 'CME Group': 'CME',\n",
       " 'CME Group Inc': 'CME',\n",
       " 'CMS Energy': 'CMS',\n",
       " 'CNG Financial': 'CNA',\n",
       " 'CNH Industrial': 'CNHI',\n",
       " 'CNL Financial': 'CNA',\n",
       " 'CNO Financial': 'CNA',\n",
       " 'CNO Financial Group': 'CNO',\n",
       " 'COCO Communications': 'CCOI',\n",
       " 'COLSA Corp': 'CRVL',\n",
       " 'CONSOL Energy': 'CNX',\n",
       " 'CPI Inc': 'CPF',\n",
       " 'CPMG Inc': 'CPF',\n",
       " 'CRH PLC': 'CRH',\n",
       " 'CRH plc': 'CRH',\n",
       " 'CRISPR Therapeutics': 'CRSP',\n",
       " 'CRT Inc': 'CRAY',\n",
       " 'CSCM Inc': 'CSPI',\n",
       " 'CVR Energy': 'CVI',\n",
       " 'CVS Health': 'CVS',\n",
       " 'DC Trust': 'DCT',\n",
       " 'DDR Corp': 'DDR',\n",
       " 'DIBC Holdings': 'SAUC',\n",
       " 'DISH Network': 'DISH',\n",
       " 'DSE Inc': 'DSW',\n",
       " 'DSS Inc': 'DSW',\n",
       " 'DTE Energy': 'DTQ',\n",
       " 'E*TRADE Financial': 'ETFC',\n",
       " 'EBI Inc': 'EBIX',\n",
       " 'EMCOR Group': 'EME',\n",
       " 'ENS Resources': 'ENRJ',\n",
       " 'EOG Resources': 'EOG',\n",
       " 'ERA': 'ERA',\n",
       " 'ESCO Electronics': 'ESP',\n",
       " 'F5 Networks': 'FFIV',\n",
       " 'FALA Technologies Inc': 'FARO',\n",
       " 'FIA Foundation': 'FFWM',\n",
       " 'FINCA International': 'IFV',\n",
       " 'FIRST': 'FRC^D',\n",
       " 'FLIR Systems': 'FLIR',\n",
       " 'GAIN Capital Group': 'GCAP',\n",
       " 'GEO Group': 'GEO',\n",
       " 'GSE Systems': 'GVP',\n",
       " 'GTS Inc': 'GTXI',\n",
       " 'H&R Block': 'HRB',\n",
       " 'HCA Healthcare': 'HCA',\n",
       " 'HCA Inc': 'HCA',\n",
       " 'HFF Inc': 'HF',\n",
       " 'HFX Inc': 'HF',\n",
       " 'HMS Holdings': 'HMSY',\n",
       " 'HP Inc': 'HPQ',\n",
       " 'HSBC Holdings': 'HSEB',\n",
       " 'HST Inc': 'HSNI',\n",
       " 'HTG Molecular Diagnostics': 'HTGM',\n",
       " 'IAC/InterActiveCorp': 'IAC',\n",
       " 'IBP Inc': 'IBIO',\n",
       " 'IBS Inc': 'IBIO',\n",
       " 'ICF International': 'ICFI',\n",
       " 'ICF Kaiser International': 'ICFI',\n",
       " 'ICIX International': 'ICFI',\n",
       " 'ICON International': 'ICFI',\n",
       " 'ICOR International': 'ICFI',\n",
       " 'ICTS International': 'ICFI',\n",
       " 'ICU Medical Inc': 'ICUI',\n",
       " 'IDACORP Inc': 'IDA',\n",
       " 'IDEXX Laboratories': 'IDXX',\n",
       " 'IMET Laboratories': 'IPXL',\n",
       " 'ING Group': 'ISP.CL',\n",
       " 'INSYS Therapeutics': 'NVIV',\n",
       " 'INTL FCStone Inc': 'INTL',\n",
       " 'ION Geophysical Corp': 'IO',\n",
       " 'IPS Inc': 'IPAS',\n",
       " 'IPSCO Inc': 'IPAS',\n",
       " 'ISP Technologies': 'XT',\n",
       " 'ISPA Technologies': 'XT',\n",
       " 'ITA Inc': 'ITT',\n",
       " 'ITRON Inc': 'ITRI',\n",
       " 'J2 Global Communications': 'JCOM',\n",
       " 'K12 Inc': 'LRN',\n",
       " 'KAR Holdings': 'KDMN',\n",
       " 'KB Home': 'KBH',\n",
       " 'KBR Inc': 'KBR',\n",
       " 'KKR & Co': 'KKR',\n",
       " 'KLA-Tencor Corp': 'KLAC',\n",
       " 'KMX Chemical': 'KMG',\n",
       " 'L Brands': 'LB',\n",
       " 'LA Partners': 'LMRKP',\n",
       " 'LA Quinta Inns': 'LQ',\n",
       " 'LC Industries': 'LCII',\n",
       " 'LEARN Coalition': 'LEA',\n",
       " 'LEEP Holdings & Balkrete Inc': 'LDOS',\n",
       " 'LHC Group': 'LHCG',\n",
       " 'LION Inc': 'LPCN',\n",
       " 'LSB Industries': 'LYTS',\n",
       " 'M Financial Group': 'MBTF',\n",
       " 'M&T Bank': 'MTB.WS',\n",
       " 'M/I Homes Inc': 'MHO',\n",
       " 'MAC Inc': 'MCHX',\n",
       " 'MAF Bancorp': 'MGYR',\n",
       " 'MBDA Inc': 'MBI',\n",
       " 'MBI Inc': 'MBI',\n",
       " 'MBIA Inc': 'MBI',\n",
       " 'MDU Resources Group': 'MDU',\n",
       " 'MEDInstill Inc': 'MED',\n",
       " 'MERIT Inc': 'MED',\n",
       " 'MG International': 'MGM',\n",
       " 'MGIC Investment Corp': 'MTG',\n",
       " 'MGM Resorts International': 'MGM',\n",
       " 'MGP Ingredients': 'MGPI',\n",
       " 'MIC Industries': 'MLR',\n",
       " 'MKS Instruments': 'MKSI',\n",
       " 'MSE Inc': 'MSCI',\n",
       " 'MTS Systems': 'MTSC',\n",
       " 'NAC International': 'NAII',\n",
       " 'NACCO Industries': 'NC',\n",
       " 'NACE International': 'NAII',\n",
       " 'NASDAQ Stock Market': 'ATEST.A',\n",
       " 'NCL Holdings': 'NCSM',\n",
       " 'NCM Holdings': 'NCSM',\n",
       " 'NEC Corp': 'NVRO',\n",
       " 'NETWORK': 'NTIP',\n",
       " 'NIC Holding': 'NODK',\n",
       " 'NIC Inc': 'EGOV',\n",
       " 'NMI Holdings': 'NMIH',\n",
       " 'NQ Mobile': 'NQ',\n",
       " 'NRG Energy': 'NRG',\n",
       " 'NVIDIA Corp': 'NVDA',\n",
       " 'NVR Inc': 'NVR',\n",
       " 'NXP Semiconductors': 'NXPI',\n",
       " 'NXP Semiconductors USA': 'NXPI',\n",
       " 'OFG Bancorp': 'OFG^D',\n",
       " 'OGE Energy': 'OGE',\n",
       " 'OM Group': 'OMC',\n",
       " 'ONEOK Inc': 'OKE',\n",
       " 'OPKO Health': 'OPK',\n",
       " 'ORBIS International': 'OFIX',\n",
       " 'ORIX Corp': 'IX',\n",
       " 'OSI Systems': 'OSIS',\n",
       " 'PBF Energy': 'PBF',\n",
       " 'PCI Communications Inc': 'CNXN',\n",
       " 'PEC Inc': 'PEP',\n",
       " 'PEF Services': 'PFSI',\n",
       " 'PHH Corp': 'PHH',\n",
       " 'PHI Inc': 'PHIIK',\n",
       " 'PIVX Solutions': 'PPSI',\n",
       " 'PNC Financial Services': 'PNC',\n",
       " 'PNM Resources': 'PNM',\n",
       " 'POMCO': 'PKX',\n",
       " 'POPPA Inc': 'BPOPM',\n",
       " 'PPG Industries': 'PPG',\n",
       " 'PRIMEDIA Inc': 'PRI',\n",
       " 'PTC Inc': 'PTC',\n",
       " 'PTC Therapeutics': 'PTCT',\n",
       " 'PTPN Inc': 'PTC',\n",
       " 'PURE Bioscience': 'PLSE',\n",
       " 'PVH Corp': 'PVH',\n",
       " 'QAI Inc': 'QADB',\n",
       " 'QEP Resources': 'QEP',\n",
       " 'RAIT Financial Trust': 'RAS',\n",
       " 'RE2 Inc': 'REIS',\n",
       " 'RED-INC': 'RHT',\n",
       " 'REL Inc': 'REIS',\n",
       " 'REMAX International': 'RDIB',\n",
       " 'REX American Resources Corp': 'REX',\n",
       " 'RH Strategies': 'RH',\n",
       " 'RPM Inc': 'RES',\n",
       " 'RPM International': 'RPM',\n",
       " 'S&P Global': 'SPGI',\n",
       " 'SAFE': 'SB^B',\n",
       " 'SAIC Inc': 'SAIA',\n",
       " 'SAP SE': 'SAP',\n",
       " 'SAVVIS Communications': 'SGA',\n",
       " 'SBC Communications': 'SBAC',\n",
       " 'SCYNEXIS Inc': 'SCYX',\n",
       " 'SEACOR Holdings': 'CKH',\n",
       " 'SG Blocks LLC': 'SGBX',\n",
       " 'SHC Inc': 'TYHT',\n",
       " 'SI International': 'SGMA',\n",
       " 'SI2 Technologies': 'SIFY',\n",
       " 'SIGA Technologies': 'SIFY',\n",
       " 'SITA Inc': 'SRVA',\n",
       " 'SJB Group': 'SJW',\n",
       " 'SK Telecom': 'SKM',\n",
       " 'SL Green Realty': 'SLG',\n",
       " 'SMART': 'SGH',\n",
       " 'SMART Technologies': 'SFS',\n",
       " 'SMS Holdings': 'SGH',\n",
       " 'SNF Inc': 'SNAP',\n",
       " 'SOLIX INC': 'SNGX',\n",
       " 'SOMS Technologies': 'SEDG',\n",
       " 'SRC Inc': 'SRCI',\n",
       " 'ST&P Communications': 'STRP',\n",
       " 'STMicroelectronics': 'STM',\n",
       " 'STN Inc': 'STN',\n",
       " 'STRONGER Inc': 'SRI',\n",
       " 'STS International': 'SXI',\n",
       " 'SVB Financial Group': 'SIVBO',\n",
       " 'SYKES Enterprises': 'SYKE',\n",
       " 'SYS Technologies': 'SNCR',\n",
       " 'T-Mobile Assn': 'TMUSP',\n",
       " 'T-Mobile USA': 'TMUSP',\n",
       " 'T2 Biosystems': 'TTOO',\n",
       " 'TCF Financial': 'TCF^D',\n",
       " 'TD Ameritrade': 'AMTD',\n",
       " 'TE Connectivity': 'TEL',\n",
       " 'TEAM Inc': 'GCI',\n",
       " 'TJX Companies': 'TJX',\n",
       " 'TOTAL SA': 'TOT',\n",
       " 'TTM Technologies': 'TTMI',\n",
       " 'TTi Technologies': 'TTMI',\n",
       " 'UBS AG': 'UBS',\n",
       " 'UMH Properties': 'UMH',\n",
       " 'UNICO': 'UNAM',\n",
       " 'US Corrections Corp': 'USG',\n",
       " 'US Ecology Corp': 'ECOL',\n",
       " 'US Ecology Idaho': 'ECOL',\n",
       " 'US Foods': 'USFD',\n",
       " 'US Inc': 'ECOL',\n",
       " 'US JVC Corp': 'USFD',\n",
       " 'US LEC Corp': 'USFD',\n",
       " 'USA Partners': 'USAC',\n",
       " 'VANTIV': 'VNTV',\n",
       " 'VIO Inc': 'VIA',\n",
       " 'VISX Inc': 'VVUS',\n",
       " 'WEC Energy Group': 'WEC',\n",
       " 'WECU Technologies': 'WSTL',\n",
       " 'WINGS Inc': 'WING',\n",
       " 'WPX Energy': 'WPXP',\n",
       " 'XOMA': 'XOMA',\n",
       " 'XPO Logistics': 'XPO',\n",
       " 'YRC Worldwide': 'YRCW',\n",
       " 'YUM! Brands': 'YUM'}"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#3. use fuzzy match algorithm \n",
    "import time\n",
    "t0 = time.time()\n",
    "\n",
    "pre=0\n",
    "name2sharecode = {}\n",
    "\n",
    "offset = 0\n",
    "limit = len(co_name)\n",
    "\n",
    "for j, co_row in co_name.iloc[offset: offset + limit].iterrows():\n",
    "    cur = round((j - offset) / limit * 100)\n",
    "    if cur > pre:\n",
    "        pre = cur\n",
    "        print(f'{cur}%...')   #give progress bar\n",
    "    \n",
    "    share_pre2 = share[share['sharename'].str.upper().str.startswith(co_row['coname'][0:2])]\n",
    "    \n",
    "    #match company names\n",
    "    for i, share_row in share_pre2.iterrows():\n",
    "        if fuzz.token_set_ratio(share_row['sharename'], co_row['coname']) > 75:\n",
    "            name2sharecode[co_row['coname']] = share_row['sharecode']\n",
    "            print(i, j)\n",
    "            break\n",
    "print(time.time() - t0)\n",
    "import json\n",
    "json.dump(name2sharecode, open('name2sharecode{}.json'.format(offset), 'w'))\n",
    "name2sharecode"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "304"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(name2sharecode)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    nopublic\n",
       "1    nopublic\n",
       "2    nopublic\n",
       "3    nopublic\n",
       "4    nopublic\n",
       "Name: result, dtype: object"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "co['result'] = [name2sharecode.get(co_row['coname'], 'nopublic') for _, co_row in co.iterrows()]\n",
    "co['result'].head()\n",
    "\n",
    "#write back"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count       222450\n",
       "unique         256\n",
       "top       nopublic\n",
       "freq        220249\n",
       "Name: result, dtype: object"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "co['result'].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>coname</th>\n",
       "      <th>money</th>\n",
       "      <th>year</th>\n",
       "      <th>comma</th>\n",
       "      <th>haha</th>\n",
       "      <th>result</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>55</th>\n",
       "      <td>1-800-Flowers.com</td>\n",
       "      <td>,0,</td>\n",
       "      <td>2009</td>\n",
       "      <td>,</td>\n",
       "      <td>A8000</td>\n",
       "      <td>FLWS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56</th>\n",
       "      <td>1-800-Flowers.com</td>\n",
       "      <td>,30000,</td>\n",
       "      <td>2010</td>\n",
       "      <td>,</td>\n",
       "      <td>A8000</td>\n",
       "      <td>FLWS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>195</th>\n",
       "      <td>2U Inc</td>\n",
       "      <td>,10000,</td>\n",
       "      <td>2015</td>\n",
       "      <td>,</td>\n",
       "      <td>Y4000</td>\n",
       "      <td>TWOU</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>196</th>\n",
       "      <td>2U Inc</td>\n",
       "      <td>,20000,</td>\n",
       "      <td>2016</td>\n",
       "      <td>,</td>\n",
       "      <td>Y4000</td>\n",
       "      <td>TWOU</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>247</th>\n",
       "      <td>3D Systems</td>\n",
       "      <td>,20000,</td>\n",
       "      <td>1998</td>\n",
       "      <td>,</td>\n",
       "      <td>Y4000</td>\n",
       "      <td>DDD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>412</th>\n",
       "      <td>8X8 Inc</td>\n",
       "      <td>,20000,</td>\n",
       "      <td>2006</td>\n",
       "      <td>,</td>\n",
       "      <td>C4100</td>\n",
       "      <td>EGHT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>413</th>\n",
       "      <td>8X8 Inc</td>\n",
       "      <td>,0,</td>\n",
       "      <td>2007</td>\n",
       "      <td>,</td>\n",
       "      <td>C4100</td>\n",
       "      <td>EGHT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>414</th>\n",
       "      <td>8X8 Inc</td>\n",
       "      <td>,0,</td>\n",
       "      <td>2008</td>\n",
       "      <td>,</td>\n",
       "      <td>C4100</td>\n",
       "      <td>EGHT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>462</th>\n",
       "      <td>A10 Networks Government</td>\n",
       "      <td>,35000,</td>\n",
       "      <td>2014</td>\n",
       "      <td>,</td>\n",
       "      <td>Y4000</td>\n",
       "      <td>ATEN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>463</th>\n",
       "      <td>A10 Networks Government</td>\n",
       "      <td>,35000,</td>\n",
       "      <td>2015</td>\n",
       "      <td>,</td>\n",
       "      <td>Y4000</td>\n",
       "      <td>ATEN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>488</th>\n",
       "      <td>AAI Corp</td>\n",
       "      <td>,420000,</td>\n",
       "      <td>1998</td>\n",
       "      <td>,</td>\n",
       "      <td>D4000</td>\n",
       "      <td>AIR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>494</th>\n",
       "      <td>AAR Corp</td>\n",
       "      <td>,0,</td>\n",
       "      <td>1998</td>\n",
       "      <td>,</td>\n",
       "      <td>T1200</td>\n",
       "      <td>AIR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>495</th>\n",
       "      <td>AAR Corp</td>\n",
       "      <td>,80000,</td>\n",
       "      <td>2001</td>\n",
       "      <td>,</td>\n",
       "      <td>D2000</td>\n",
       "      <td>AIR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>496</th>\n",
       "      <td>AAR Corp</td>\n",
       "      <td>,120000,</td>\n",
       "      <td>2002</td>\n",
       "      <td>,</td>\n",
       "      <td>D2000</td>\n",
       "      <td>AIR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>497</th>\n",
       "      <td>AAR Corp</td>\n",
       "      <td>,120000,</td>\n",
       "      <td>2003</td>\n",
       "      <td>,</td>\n",
       "      <td>D2000</td>\n",
       "      <td>AIR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>498</th>\n",
       "      <td>AAR Corp</td>\n",
       "      <td>,120000,</td>\n",
       "      <td>2004</td>\n",
       "      <td>,</td>\n",
       "      <td>D2000</td>\n",
       "      <td>AIR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>499</th>\n",
       "      <td>AAR Corp</td>\n",
       "      <td>,160000,</td>\n",
       "      <td>2005</td>\n",
       "      <td>,</td>\n",
       "      <td>D2000</td>\n",
       "      <td>AIR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>500</th>\n",
       "      <td>AAR Corp</td>\n",
       "      <td>,160000,</td>\n",
       "      <td>2006</td>\n",
       "      <td>,</td>\n",
       "      <td>D2000</td>\n",
       "      <td>AIR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>501</th>\n",
       "      <td>AAR Corp</td>\n",
       "      <td>,160000,</td>\n",
       "      <td>2007</td>\n",
       "      <td>,</td>\n",
       "      <td>D2000</td>\n",
       "      <td>AIR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>502</th>\n",
       "      <td>AAR Corp</td>\n",
       "      <td>,160000,</td>\n",
       "      <td>2008</td>\n",
       "      <td>,</td>\n",
       "      <td>D2000</td>\n",
       "      <td>AIR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>503</th>\n",
       "      <td>AAR Corp</td>\n",
       "      <td>,160000,</td>\n",
       "      <td>2009</td>\n",
       "      <td>,</td>\n",
       "      <td>D2000</td>\n",
       "      <td>AIR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>504</th>\n",
       "      <td>AAR Corp</td>\n",
       "      <td>,150000,</td>\n",
       "      <td>2010</td>\n",
       "      <td>,</td>\n",
       "      <td>D2000</td>\n",
       "      <td>AIR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>505</th>\n",
       "      <td>AAR Corp</td>\n",
       "      <td>,110000,</td>\n",
       "      <td>2011</td>\n",
       "      <td>,</td>\n",
       "      <td>D2000</td>\n",
       "      <td>AIR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>506</th>\n",
       "      <td>AAR Corp</td>\n",
       "      <td>,110000,</td>\n",
       "      <td>2012</td>\n",
       "      <td>,</td>\n",
       "      <td>D2000</td>\n",
       "      <td>AIR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>507</th>\n",
       "      <td>AAR Corp</td>\n",
       "      <td>,140000,</td>\n",
       "      <td>2013</td>\n",
       "      <td>,</td>\n",
       "      <td>D2000</td>\n",
       "      <td>AIR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>508</th>\n",
       "      <td>AAR Corp</td>\n",
       "      <td>,90000,</td>\n",
       "      <td>2014</td>\n",
       "      <td>,</td>\n",
       "      <td>D2000</td>\n",
       "      <td>AIR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>509</th>\n",
       "      <td>AAR Corp</td>\n",
       "      <td>,140000,</td>\n",
       "      <td>2015</td>\n",
       "      <td>,</td>\n",
       "      <td>D2000</td>\n",
       "      <td>AIR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>510</th>\n",
       "      <td>AAR Corp</td>\n",
       "      <td>,90000,</td>\n",
       "      <td>2016</td>\n",
       "      <td>,</td>\n",
       "      <td>D2000</td>\n",
       "      <td>AIR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>770</th>\n",
       "      <td>ABIOMED Inc</td>\n",
       "      <td>,160000,</td>\n",
       "      <td>2005</td>\n",
       "      <td>,</td>\n",
       "      <td>H4100</td>\n",
       "      <td>ABMD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>802</th>\n",
       "      <td>ABM Industries</td>\n",
       "      <td>,50000,</td>\n",
       "      <td>2010</td>\n",
       "      <td>,</td>\n",
       "      <td>f4500</td>\n",
       "      <td>ABM</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>221050</th>\n",
       "      <td>XPO Logistics</td>\n",
       "      <td>,60000,</td>\n",
       "      <td>2015</td>\n",
       "      <td>,</td>\n",
       "      <td>Y4000</td>\n",
       "      <td>XPO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>221051</th>\n",
       "      <td>XPO Logistics</td>\n",
       "      <td>,30000,</td>\n",
       "      <td>2016</td>\n",
       "      <td>,</td>\n",
       "      <td>Y4000</td>\n",
       "      <td>XPO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>221720</th>\n",
       "      <td>YRC Worldwide</td>\n",
       "      <td>,800000,</td>\n",
       "      <td>2009</td>\n",
       "      <td>,</td>\n",
       "      <td>T3100</td>\n",
       "      <td>YRCW</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>221721</th>\n",
       "      <td>YRC Worldwide</td>\n",
       "      <td>,645000,</td>\n",
       "      <td>2010</td>\n",
       "      <td>,</td>\n",
       "      <td>T3100</td>\n",
       "      <td>YRCW</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>221722</th>\n",
       "      <td>YRC Worldwide</td>\n",
       "      <td>,637500,</td>\n",
       "      <td>2011</td>\n",
       "      <td>,</td>\n",
       "      <td>T3100</td>\n",
       "      <td>YRCW</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>221723</th>\n",
       "      <td>YRC Worldwide</td>\n",
       "      <td>,500000,</td>\n",
       "      <td>2012</td>\n",
       "      <td>,</td>\n",
       "      <td>T3100</td>\n",
       "      <td>YRCW</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>221724</th>\n",
       "      <td>YRC Worldwide</td>\n",
       "      <td>,620000,</td>\n",
       "      <td>2013</td>\n",
       "      <td>,</td>\n",
       "      <td>T3100</td>\n",
       "      <td>YRCW</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>221725</th>\n",
       "      <td>YRC Worldwide</td>\n",
       "      <td>,320000,</td>\n",
       "      <td>2014</td>\n",
       "      <td>,</td>\n",
       "      <td>T3100</td>\n",
       "      <td>YRCW</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>221726</th>\n",
       "      <td>YRC Worldwide</td>\n",
       "      <td>,130000,</td>\n",
       "      <td>2015</td>\n",
       "      <td>,</td>\n",
       "      <td>T3100</td>\n",
       "      <td>YRCW</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>221727</th>\n",
       "      <td>YRC Worldwide</td>\n",
       "      <td>,90000,</td>\n",
       "      <td>2016</td>\n",
       "      <td>,</td>\n",
       "      <td>T3100</td>\n",
       "      <td>YRCW</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>221781</th>\n",
       "      <td>YUM! Brands</td>\n",
       "      <td>,490683,</td>\n",
       "      <td>1998</td>\n",
       "      <td>,</td>\n",
       "      <td>G2900</td>\n",
       "      <td>YUM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>221782</th>\n",
       "      <td>YUM! Brands</td>\n",
       "      <td>,520000,</td>\n",
       "      <td>1999</td>\n",
       "      <td>,</td>\n",
       "      <td>G2900</td>\n",
       "      <td>YUM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>221783</th>\n",
       "      <td>YUM! Brands</td>\n",
       "      <td>,550000,</td>\n",
       "      <td>2000</td>\n",
       "      <td>,</td>\n",
       "      <td>G2900</td>\n",
       "      <td>YUM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>221784</th>\n",
       "      <td>YUM! Brands</td>\n",
       "      <td>,600000,</td>\n",
       "      <td>2001</td>\n",
       "      <td>,</td>\n",
       "      <td>G2900</td>\n",
       "      <td>YUM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>221785</th>\n",
       "      <td>YUM! Brands</td>\n",
       "      <td>,660000,</td>\n",
       "      <td>2002</td>\n",
       "      <td>,</td>\n",
       "      <td>G2900</td>\n",
       "      <td>YUM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>221786</th>\n",
       "      <td>YUM! Brands</td>\n",
       "      <td>,570000,</td>\n",
       "      <td>2003</td>\n",
       "      <td>,</td>\n",
       "      <td>G2900</td>\n",
       "      <td>YUM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>221787</th>\n",
       "      <td>YUM! Brands</td>\n",
       "      <td>,0,</td>\n",
       "      <td>2003</td>\n",
       "      <td>,</td>\n",
       "      <td>G2900</td>\n",
       "      <td>YUM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>221788</th>\n",
       "      <td>YUM! Brands</td>\n",
       "      <td>,690000,</td>\n",
       "      <td>2004</td>\n",
       "      <td>,</td>\n",
       "      <td>G2900</td>\n",
       "      <td>YUM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>221789</th>\n",
       "      <td>YUM! Brands</td>\n",
       "      <td>,700000,</td>\n",
       "      <td>2005</td>\n",
       "      <td>,</td>\n",
       "      <td>G2900</td>\n",
       "      <td>YUM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>221790</th>\n",
       "      <td>YUM! Brands</td>\n",
       "      <td>,903500,</td>\n",
       "      <td>2006</td>\n",
       "      <td>,</td>\n",
       "      <td>G2900</td>\n",
       "      <td>YUM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>221791</th>\n",
       "      <td>YUM! Brands</td>\n",
       "      <td>,711795,</td>\n",
       "      <td>2007</td>\n",
       "      <td>,</td>\n",
       "      <td>G2900</td>\n",
       "      <td>YUM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>221792</th>\n",
       "      <td>YUM! Brands</td>\n",
       "      <td>,870000,</td>\n",
       "      <td>2008</td>\n",
       "      <td>,</td>\n",
       "      <td>G2900</td>\n",
       "      <td>YUM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>221793</th>\n",
       "      <td>YUM! Brands</td>\n",
       "      <td>,1430000,</td>\n",
       "      <td>2009</td>\n",
       "      <td>,</td>\n",
       "      <td>G2900</td>\n",
       "      <td>YUM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>221794</th>\n",
       "      <td>YUM! Brands</td>\n",
       "      <td>,1400000,</td>\n",
       "      <td>2010</td>\n",
       "      <td>,</td>\n",
       "      <td>G2900</td>\n",
       "      <td>YUM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>221795</th>\n",
       "      <td>YUM! Brands</td>\n",
       "      <td>,1140000,</td>\n",
       "      <td>2011</td>\n",
       "      <td>,</td>\n",
       "      <td>G2900</td>\n",
       "      <td>YUM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>221796</th>\n",
       "      <td>YUM! Brands</td>\n",
       "      <td>,670000,</td>\n",
       "      <td>2012</td>\n",
       "      <td>,</td>\n",
       "      <td>G2900</td>\n",
       "      <td>YUM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>221797</th>\n",
       "      <td>YUM! Brands</td>\n",
       "      <td>,690000,</td>\n",
       "      <td>2013</td>\n",
       "      <td>,</td>\n",
       "      <td>G2900</td>\n",
       "      <td>YUM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>221798</th>\n",
       "      <td>YUM! Brands</td>\n",
       "      <td>,867000,</td>\n",
       "      <td>2014</td>\n",
       "      <td>,</td>\n",
       "      <td>G2900</td>\n",
       "      <td>YUM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>221799</th>\n",
       "      <td>YUM! Brands</td>\n",
       "      <td>,1607985,</td>\n",
       "      <td>2015</td>\n",
       "      <td>,</td>\n",
       "      <td>G2900</td>\n",
       "      <td>YUM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>221800</th>\n",
       "      <td>YUM! Brands</td>\n",
       "      <td>,1278203,</td>\n",
       "      <td>2016</td>\n",
       "      <td>,</td>\n",
       "      <td>G2900</td>\n",
       "      <td>YUM</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2201 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                         coname      money  year comma   haha result\n",
       "55            1-800-Flowers.com        ,0,  2009     ,  A8000   FLWS\n",
       "56            1-800-Flowers.com    ,30000,  2010     ,  A8000   FLWS\n",
       "195                      2U Inc    ,10000,  2015     ,  Y4000   TWOU\n",
       "196                      2U Inc    ,20000,  2016     ,  Y4000   TWOU\n",
       "247                  3D Systems    ,20000,  1998     ,  Y4000    DDD\n",
       "412                     8X8 Inc    ,20000,  2006     ,  C4100   EGHT\n",
       "413                     8X8 Inc        ,0,  2007     ,  C4100   EGHT\n",
       "414                     8X8 Inc        ,0,  2008     ,  C4100   EGHT\n",
       "462     A10 Networks Government    ,35000,  2014     ,  Y4000   ATEN\n",
       "463     A10 Networks Government    ,35000,  2015     ,  Y4000   ATEN\n",
       "488                    AAI Corp   ,420000,  1998     ,  D4000    AIR\n",
       "494                    AAR Corp        ,0,  1998     ,  T1200    AIR\n",
       "495                    AAR Corp    ,80000,  2001     ,  D2000    AIR\n",
       "496                    AAR Corp   ,120000,  2002     ,  D2000    AIR\n",
       "497                    AAR Corp   ,120000,  2003     ,  D2000    AIR\n",
       "498                    AAR Corp   ,120000,  2004     ,  D2000    AIR\n",
       "499                    AAR Corp   ,160000,  2005     ,  D2000    AIR\n",
       "500                    AAR Corp   ,160000,  2006     ,  D2000    AIR\n",
       "501                    AAR Corp   ,160000,  2007     ,  D2000    AIR\n",
       "502                    AAR Corp   ,160000,  2008     ,  D2000    AIR\n",
       "503                    AAR Corp   ,160000,  2009     ,  D2000    AIR\n",
       "504                    AAR Corp   ,150000,  2010     ,  D2000    AIR\n",
       "505                    AAR Corp   ,110000,  2011     ,  D2000    AIR\n",
       "506                    AAR Corp   ,110000,  2012     ,  D2000    AIR\n",
       "507                    AAR Corp   ,140000,  2013     ,  D2000    AIR\n",
       "508                    AAR Corp    ,90000,  2014     ,  D2000    AIR\n",
       "509                    AAR Corp   ,140000,  2015     ,  D2000    AIR\n",
       "510                    AAR Corp    ,90000,  2016     ,  D2000    AIR\n",
       "770                 ABIOMED Inc   ,160000,  2005     ,  H4100   ABMD\n",
       "802              ABM Industries    ,50000,  2010     ,  f4500    ABM\n",
       "...                         ...        ...   ...   ...    ...    ...\n",
       "221050            XPO Logistics    ,60000,  2015     ,  Y4000    XPO\n",
       "221051            XPO Logistics    ,30000,  2016     ,  Y4000    XPO\n",
       "221720            YRC Worldwide   ,800000,  2009     ,  T3100   YRCW\n",
       "221721            YRC Worldwide   ,645000,  2010     ,  T3100   YRCW\n",
       "221722            YRC Worldwide   ,637500,  2011     ,  T3100   YRCW\n",
       "221723            YRC Worldwide   ,500000,  2012     ,  T3100   YRCW\n",
       "221724            YRC Worldwide   ,620000,  2013     ,  T3100   YRCW\n",
       "221725            YRC Worldwide   ,320000,  2014     ,  T3100   YRCW\n",
       "221726            YRC Worldwide   ,130000,  2015     ,  T3100   YRCW\n",
       "221727            YRC Worldwide    ,90000,  2016     ,  T3100   YRCW\n",
       "221781              YUM! Brands   ,490683,  1998     ,  G2900    YUM\n",
       "221782              YUM! Brands   ,520000,  1999     ,  G2900    YUM\n",
       "221783              YUM! Brands   ,550000,  2000     ,  G2900    YUM\n",
       "221784              YUM! Brands   ,600000,  2001     ,  G2900    YUM\n",
       "221785              YUM! Brands   ,660000,  2002     ,  G2900    YUM\n",
       "221786              YUM! Brands   ,570000,  2003     ,  G2900    YUM\n",
       "221787              YUM! Brands        ,0,  2003     ,  G2900    YUM\n",
       "221788              YUM! Brands   ,690000,  2004     ,  G2900    YUM\n",
       "221789              YUM! Brands   ,700000,  2005     ,  G2900    YUM\n",
       "221790              YUM! Brands   ,903500,  2006     ,  G2900    YUM\n",
       "221791              YUM! Brands   ,711795,  2007     ,  G2900    YUM\n",
       "221792              YUM! Brands   ,870000,  2008     ,  G2900    YUM\n",
       "221793              YUM! Brands  ,1430000,  2009     ,  G2900    YUM\n",
       "221794              YUM! Brands  ,1400000,  2010     ,  G2900    YUM\n",
       "221795              YUM! Brands  ,1140000,  2011     ,  G2900    YUM\n",
       "221796              YUM! Brands   ,670000,  2012     ,  G2900    YUM\n",
       "221797              YUM! Brands   ,690000,  2013     ,  G2900    YUM\n",
       "221798              YUM! Brands   ,867000,  2014     ,  G2900    YUM\n",
       "221799              YUM! Brands  ,1607985,  2015     ,  G2900    YUM\n",
       "221800              YUM! Brands  ,1278203,  2016     ,  G2900    YUM\n",
       "\n",
       "[2201 rows x 6 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "co[co['result'] != 'nopublic']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "co.to_csv(\"./jy_project.csv\")\n",
    "#write to csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "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.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}