CleanData.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Cleaning"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Navigate to:  [acquisitions](#acquisitions)\n",
    " | [application](#application)\n",
    " | [assignee](#assignee) \n",
    " | [cpc_current](#cpc_current)\n",
    " | [group](#group)\n",
    " | [subgroup](#subgroup)\n",
    " | [subsection](#subsection)\n",
    " | [location](#location)\n",
    " | [location_assignee](#location_assignee)\n",
    " | [us_term_of_grant](#us_term_of_grant)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Summary"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    " - acquisitions = \"acquisitions_clean.csv\"\n",
    " - assignee = \"assignee_clean.csv\"\n",
    " - cpc_current = \"cpc_current_clean.csv\"\n",
    " - group = \"group_clean.tsv\"  *\n",
    " - subgroup = \"subgroup_clean.tsv\"   *\n",
    " - subsection = \"subsection_clean.tsv\"  *\n",
    " - location = \"location_clean.tsv\"  *\n",
    " - location_assignee = \"location_assignee_clean.csv\"\n",
    " - us_term_of_grant = \"us_term_of_grant_clean.csv\" <br>\n",
    " <i>* .tsv because one or more columns contain commas </i>\n",
    "\n",
    "companies = [\"Google\", \"Microsoft\", \"Facebook\", \"Apple\", \"Yahoo\", \"Twitter\", \"IBM\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## acquisitions\n",
    "[acquisitions](#acquisitions)\n",
    " | [application](#application)\n",
    " | [assignee](#assignee) \n",
    " | [cpc_current](#cpc_current)\n",
    " | [group](#group)\n",
    " | [subgroup](#subgroup)\n",
    " | [subsection](#subsection)\n",
    " | [location](#location)\n",
    " | [location_assignee](#location_assignee)\n",
    " | [us_term_of_grant](#us_term_of_grant)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "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>AcquisitionID</th>\n",
       "      <th>AcquisitionMonth</th>\n",
       "      <th>AcquisitionMonthDate</th>\n",
       "      <th>AcquisitionYear</th>\n",
       "      <th>Company</th>\n",
       "      <th>Business</th>\n",
       "      <th>Country</th>\n",
       "      <th>Value (USD)</th>\n",
       "      <th>Derived products</th>\n",
       "      <th>ParentCompany</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ACQ99</td>\n",
       "      <td>November</td>\n",
       "      <td>11.0</td>\n",
       "      <td>2015</td>\n",
       "      <td>bebop</td>\n",
       "      <td>Cloud software</td>\n",
       "      <td>USA</td>\n",
       "      <td>380000000.0</td>\n",
       "      <td>Google Cloud Platform</td>\n",
       "      <td>Google</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ACQ98</td>\n",
       "      <td>November</td>\n",
       "      <td>11.0</td>\n",
       "      <td>2015</td>\n",
       "      <td>Fly Labs</td>\n",
       "      <td>Video editing</td>\n",
       "      <td>USA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Google Photos</td>\n",
       "      <td>Google</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ACQ97</td>\n",
       "      <td>December</td>\n",
       "      <td>8.0</td>\n",
       "      <td>2015</td>\n",
       "      <td>Clearleap</td>\n",
       "      <td>Cloud-based video management</td>\n",
       "      <td>USA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>IBM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ACQ96</td>\n",
       "      <td>December</td>\n",
       "      <td>18.0</td>\n",
       "      <td>2015</td>\n",
       "      <td>Metanautix</td>\n",
       "      <td>Big Data Analytics</td>\n",
       "      <td>USA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Microsoft</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ACQ95</td>\n",
       "      <td>December</td>\n",
       "      <td>21.0</td>\n",
       "      <td>2015</td>\n",
       "      <td>Talko, Inc.</td>\n",
       "      <td>Mobile communications</td>\n",
       "      <td>USA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Microsoft</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  AcquisitionID AcquisitionMonth  AcquisitionMonthDate  AcquisitionYear  \\\n",
       "0         ACQ99         November                  11.0             2015   \n",
       "1         ACQ98         November                  11.0             2015   \n",
       "2         ACQ97         December                   8.0             2015   \n",
       "3         ACQ96         December                  18.0             2015   \n",
       "4         ACQ95         December                  21.0             2015   \n",
       "\n",
       "       Company                      Business Country  Value (USD)  \\\n",
       "0        bebop                Cloud software     USA  380000000.0   \n",
       "1     Fly Labs                 Video editing     USA          NaN   \n",
       "2    Clearleap  Cloud-based video management     USA          NaN   \n",
       "3   Metanautix            Big Data Analytics     USA          NaN   \n",
       "4  Talko, Inc.         Mobile communications     USA          NaN   \n",
       "\n",
       "        Derived products ParentCompany  \n",
       "0  Google Cloud Platform        Google  \n",
       "1          Google Photos        Google  \n",
       "2                    NaN           IBM  \n",
       "3                    NaN     Microsoft  \n",
       "4                    NaN     Microsoft  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# original data\n",
    "dataAcquisitions = pd.read_csv('acquisitions.csv', header=0)\n",
    "dfAcquisitions = pd.DataFrame(dataAcquisitions)\n",
    "dfAcquisitions.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "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>AcquisitionYear</th>\n",
       "      <th>ChildCompany</th>\n",
       "      <th>ParentCompany</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2015</td>\n",
       "      <td>bebop</td>\n",
       "      <td>Google</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2015</td>\n",
       "      <td>Fly Labs</td>\n",
       "      <td>Google</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2015</td>\n",
       "      <td>Clearleap</td>\n",
       "      <td>IBM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2015</td>\n",
       "      <td>Metanautix</td>\n",
       "      <td>Microsoft</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2015</td>\n",
       "      <td>Talko, Inc.</td>\n",
       "      <td>Microsoft</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   AcquisitionYear ChildCompany ParentCompany\n",
       "0             2015        bebop        Google\n",
       "1             2015     Fly Labs        Google\n",
       "2             2015    Clearleap           IBM\n",
       "3             2015   Metanautix     Microsoft\n",
       "4             2015  Talko, Inc.     Microsoft"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# clean table and printing out to csv\n",
    "acquisitions = pd.DataFrame(dfAcquisitions).copy(deep=True)\n",
    "acquisitions = acquisitions.drop(columns=['AcquisitionID',\n",
    "                                          'AcquisitionMonth',\n",
    "                                          'AcquisitionMonthDate',\n",
    "                                          'Country',\n",
    "                                          'Business',\n",
    "                                          'Value (USD)',\n",
    "                                          'Derived products'])\n",
    "acquisitions = acquisitions.rename(columns={\"Company\": \"ChildCompany\"})\n",
    "acquisitions.to_csv('acquisitions_clean.csv', index=False, sep=',', encoding='utf-8')\n",
    "acquisitions.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## application\n",
    "[acquisitions](#acquisitions)\n",
    " | [application](#application)\n",
    " | [assignee](#assignee) \n",
    " | [cpc_current](#cpc_current)\n",
    " | [group](#group)\n",
    " | [subgroup](#subgroup)\n",
    " | [subsection](#subsection)\n",
    " | [location](#location)\n",
    " | [location_assignee](#location_assignee)\n",
    " | [us_term_of_grant](#us_term_of_grant)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/anaconda3/lib/python3.6/site-packages/IPython/core/interactiveshell.py:2785: DtypeWarning: Columns (2,3) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    },
    {
     "data": {
      "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>id</th>\n",
       "      <th>patent_id</th>\n",
       "      <th>series_code</th>\n",
       "      <th>number</th>\n",
       "      <th>country</th>\n",
       "      <th>date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>02/002761</td>\n",
       "      <td>D345393</td>\n",
       "      <td>2</td>\n",
       "      <td>2002761</td>\n",
       "      <td>US</td>\n",
       "      <td>1992-12-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>02/007691</td>\n",
       "      <td>5164715</td>\n",
       "      <td>2</td>\n",
       "      <td>2007691</td>\n",
       "      <td>US</td>\n",
       "      <td>1990-04-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>02/010248</td>\n",
       "      <td>5177974</td>\n",
       "      <td>2</td>\n",
       "      <td>2010248</td>\n",
       "      <td>US</td>\n",
       "      <td>1988-06-23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>02/020141</td>\n",
       "      <td>5379515</td>\n",
       "      <td>2</td>\n",
       "      <td>2020141</td>\n",
       "      <td>US</td>\n",
       "      <td>1994-02-16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>02/027172</td>\n",
       "      <td>5264790</td>\n",
       "      <td>2</td>\n",
       "      <td>2027172</td>\n",
       "      <td>US</td>\n",
       "      <td>1991-07-01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          id patent_id series_code   number country        date\n",
       "0  02/002761   D345393           2  2002761      US  1992-12-21\n",
       "1  02/007691   5164715           2  2007691      US  1990-04-10\n",
       "2  02/010248   5177974           2  2010248      US  1988-06-23\n",
       "3  02/020141   5379515           2  2020141      US  1994-02-16\n",
       "4  02/027172   5264790           2  2027172      US  1991-07-01"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# original data\n",
    "dataApplication = pd.read_csv('application.tsv', sep=\"\\t\", header=0)\n",
    "dfApplication = pd.DataFrame(dataApplication)\n",
    "dfApplication.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "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>patent_id</th>\n",
       "      <th>country</th>\n",
       "      <th>date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>D345393</td>\n",
       "      <td>US</td>\n",
       "      <td>1992-12-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5164715</td>\n",
       "      <td>US</td>\n",
       "      <td>1990-04-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5177974</td>\n",
       "      <td>US</td>\n",
       "      <td>1988-06-23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5379515</td>\n",
       "      <td>US</td>\n",
       "      <td>1994-02-16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5264790</td>\n",
       "      <td>US</td>\n",
       "      <td>1991-07-01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  patent_id country        date\n",
       "0   D345393      US  1992-12-21\n",
       "1   5164715      US  1990-04-10\n",
       "2   5177974      US  1988-06-23\n",
       "3   5379515      US  1994-02-16\n",
       "4   5264790      US  1991-07-01"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# clean table and printing out to csv\n",
    "application = pd.DataFrame(dfApplication).copy(deep=True)\n",
    "application = application.drop(columns=['id',\n",
    "                                        'series_code',\n",
    "                                        'number'])\n",
    "application.to_csv('application_clean.csv', index=False, sep=',', encoding='utf-8')\n",
    "application.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## assignee\n",
    "[acquisitions](#acquisitions)\n",
    " | [application](#application)\n",
    " | [assignee](#assignee) \n",
    " | [cpc_current](#cpc_current)\n",
    " | [group](#group)\n",
    " | [subgroup](#subgroup)\n",
    " | [subsection](#subsection)\n",
    " | [location](#location)\n",
    " | [location_assignee](#location_assignee)\n",
    " | [us_term_of_grant](#us_term_of_grant)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "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>uuid</th>\n",
       "      <th>patent_id</th>\n",
       "      <th>assignee_id</th>\n",
       "      <th>rawlocation_id</th>\n",
       "      <th>type</th>\n",
       "      <th>name_first</th>\n",
       "      <th>name_last</th>\n",
       "      <th>organization</th>\n",
       "      <th>sequence</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0000p94wkezw94s8cz7dbxlvz</td>\n",
       "      <td>5856666</td>\n",
       "      <td>eaa92f175be7bfb71011f17eafb1e71f</td>\n",
       "      <td>orskbf54s58e97lkmw8na5rpx</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>U.S. Philips Corporation</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>00013vk881wap9u4mbo7lwwhp</td>\n",
       "      <td>5204210</td>\n",
       "      <td>e572ad43a89039b0d72acc4ce970a33f</td>\n",
       "      <td>mue862v5lcjdhzqqk86ei75kj</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Xerox Corporation</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>000192sn2u10kzpikl4s7h3r0</td>\n",
       "      <td>5302149</td>\n",
       "      <td>8ce825a978eebf26ad2c13de6e370bb3</td>\n",
       "      <td>o1h9dqdv0yq7dt1b1vmrcal9h</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Commonwealth Scientific &amp; Industrial Research ...</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0001ycvv6sz1ju07ss99nhxi1</td>\n",
       "      <td>9104354</td>\n",
       "      <td>6c00cb129070696ef109f6264da00318</td>\n",
       "      <td>rspbpqcajvm09r1ew9mgnpx37</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Canon Kabushiki Kaisha</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0001z7ws4m14aqdb3tv99u550</td>\n",
       "      <td>6584517</td>\n",
       "      <td>dabf354c29a6ebba31f54b9ed042241d</td>\n",
       "      <td>l1gyelp5jcg0hakk9smmhsdgr</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Cypress Semiconductor Corp.</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        uuid patent_id                       assignee_id  \\\n",
       "0  0000p94wkezw94s8cz7dbxlvz   5856666  eaa92f175be7bfb71011f17eafb1e71f   \n",
       "1  00013vk881wap9u4mbo7lwwhp   5204210  e572ad43a89039b0d72acc4ce970a33f   \n",
       "2  000192sn2u10kzpikl4s7h3r0   5302149  8ce825a978eebf26ad2c13de6e370bb3   \n",
       "3  0001ycvv6sz1ju07ss99nhxi1   9104354  6c00cb129070696ef109f6264da00318   \n",
       "4  0001z7ws4m14aqdb3tv99u550   6584517  dabf354c29a6ebba31f54b9ed042241d   \n",
       "\n",
       "              rawlocation_id  type name_first name_last  \\\n",
       "0  orskbf54s58e97lkmw8na5rpx     2        NaN       NaN   \n",
       "1  mue862v5lcjdhzqqk86ei75kj     2        NaN       NaN   \n",
       "2  o1h9dqdv0yq7dt1b1vmrcal9h     3        NaN       NaN   \n",
       "3  rspbpqcajvm09r1ew9mgnpx37     3        NaN       NaN   \n",
       "4  l1gyelp5jcg0hakk9smmhsdgr     2        NaN       NaN   \n",
       "\n",
       "                                        organization  sequence  \n",
       "0                           U.S. Philips Corporation         0  \n",
       "1                                  Xerox Corporation         0  \n",
       "2  Commonwealth Scientific & Industrial Research ...         1  \n",
       "3                             Canon Kabushiki Kaisha         0  \n",
       "4                        Cypress Semiconductor Corp.         0  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dataAssignee = pd.read_csv('rawassignee.tsv', sep='\\t', header=0)\n",
    "dfAssignee = pd.DataFrame(dataAssignee)\n",
    "dfAssignee.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "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>patent_id</th>\n",
       "      <th>assignee_id</th>\n",
       "      <th>organization</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5856666</td>\n",
       "      <td>eaa92f175be7bfb71011f17eafb1e71f</td>\n",
       "      <td>U.S. Philips Corporation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5204210</td>\n",
       "      <td>e572ad43a89039b0d72acc4ce970a33f</td>\n",
       "      <td>Xerox Corporation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5302149</td>\n",
       "      <td>8ce825a978eebf26ad2c13de6e370bb3</td>\n",
       "      <td>Commonwealth Scientific &amp; Industrial Research ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>9104354</td>\n",
       "      <td>6c00cb129070696ef109f6264da00318</td>\n",
       "      <td>Canon Kabushiki Kaisha</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6584517</td>\n",
       "      <td>dabf354c29a6ebba31f54b9ed042241d</td>\n",
       "      <td>Cypress Semiconductor Corp.</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  patent_id                       assignee_id  \\\n",
       "0   5856666  eaa92f175be7bfb71011f17eafb1e71f   \n",
       "1   5204210  e572ad43a89039b0d72acc4ce970a33f   \n",
       "2   5302149  8ce825a978eebf26ad2c13de6e370bb3   \n",
       "3   9104354  6c00cb129070696ef109f6264da00318   \n",
       "4   6584517  dabf354c29a6ebba31f54b9ed042241d   \n",
       "\n",
       "                                        organization  \n",
       "0                           U.S. Philips Corporation  \n",
       "1                                  Xerox Corporation  \n",
       "2  Commonwealth Scientific & Industrial Research ...  \n",
       "3                             Canon Kabushiki Kaisha  \n",
       "4                        Cypress Semiconductor Corp.  "
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# clean table and printing out to csv\n",
    "assignee = pd.DataFrame(dfAssignee).copy(deep=True)\n",
    "assignee = assignee.drop(columns=['uuid',\n",
    "                                  'type',\n",
    "                                  'name_first',\n",
    "                                  'name_last',\n",
    "                                  'sequence'])\n",
    "assignee.to_csv('assignee_clean.tsv', index=False, sep='\\t', encoding='utf-8')\n",
    "assignee.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## cpc_current\n",
    "[acquisitions](#acquisitions)\n",
    " | [application](#application)\n",
    " | [assignee](#assignee) \n",
    " | [cpc_current](#cpc_current)\n",
    " | [group](#group)\n",
    " | [subgroup](#subgroup)\n",
    " | [subsection](#subsection)\n",
    " | [location](#location)\n",
    " | [location_assignee](#location_assignee)\n",
    " | [us_term_of_grant](#us_term_of_grant)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "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>uuid</th>\n",
       "      <th>patent_id</th>\n",
       "      <th>section_id</th>\n",
       "      <th>subsection_id</th>\n",
       "      <th>group_id</th>\n",
       "      <th>subgroup_id</th>\n",
       "      <th>category</th>\n",
       "      <th>sequence</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>pkscb392v7o62jgv5e3tdyct2</td>\n",
       "      <td>3930272</td>\n",
       "      <td>A</td>\n",
       "      <td>A47</td>\n",
       "      <td>A47D</td>\n",
       "      <td>A47D7/02</td>\n",
       "      <td>inventional</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>s3x865wlce64sbxds2ubgheu4</td>\n",
       "      <td>3930272</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y10</td>\n",
       "      <td>Y10T</td>\n",
       "      <td>Y10T403/32451</td>\n",
       "      <td>additional</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>638qht0an0gg7dc1ub3yeexm2</td>\n",
       "      <td>3930273</td>\n",
       "      <td>A</td>\n",
       "      <td>A61</td>\n",
       "      <td>A61G</td>\n",
       "      <td>A61G7/0507</td>\n",
       "      <td>inventional</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>34qqyersco0qh8jsn9bhdxy88</td>\n",
       "      <td>3930273</td>\n",
       "      <td>A</td>\n",
       "      <td>A61</td>\n",
       "      <td>A61G</td>\n",
       "      <td>A61G7/0509</td>\n",
       "      <td>inventional</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>sefi1no1nlmw0jjn82s68bhmq</td>\n",
       "      <td>3930274</td>\n",
       "      <td>B</td>\n",
       "      <td>B63</td>\n",
       "      <td>B63B</td>\n",
       "      <td>B63B7/085</td>\n",
       "      <td>inventional</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        uuid  patent_id section_id subsection_id group_id  \\\n",
       "0  pkscb392v7o62jgv5e3tdyct2    3930272          A           A47     A47D   \n",
       "1  s3x865wlce64sbxds2ubgheu4    3930272          Y           Y10     Y10T   \n",
       "2  638qht0an0gg7dc1ub3yeexm2    3930273          A           A61     A61G   \n",
       "3  34qqyersco0qh8jsn9bhdxy88    3930273          A           A61     A61G   \n",
       "4  sefi1no1nlmw0jjn82s68bhmq    3930274          B           B63     B63B   \n",
       "\n",
       "     subgroup_id     category  sequence  \n",
       "0       A47D7/02  inventional         0  \n",
       "1  Y10T403/32451   additional         1  \n",
       "2     A61G7/0507  inventional         0  \n",
       "3     A61G7/0509  inventional         1  \n",
       "4      B63B7/085  inventional         0  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dataCPC = pd.read_csv('cpc_current.tsv', sep='\\t', header=0)\n",
    "dfCPC = pd.DataFrame(dataCPC)\n",
    "dfCPC.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "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>patent_id</th>\n",
       "      <th>section_id</th>\n",
       "      <th>subsection_id</th>\n",
       "      <th>group_id</th>\n",
       "      <th>subgroup_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3930272</td>\n",
       "      <td>A</td>\n",
       "      <td>A47</td>\n",
       "      <td>A47D</td>\n",
       "      <td>A47D7/02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3930272</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y10</td>\n",
       "      <td>Y10T</td>\n",
       "      <td>Y10T403/32451</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3930273</td>\n",
       "      <td>A</td>\n",
       "      <td>A61</td>\n",
       "      <td>A61G</td>\n",
       "      <td>A61G7/0507</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3930273</td>\n",
       "      <td>A</td>\n",
       "      <td>A61</td>\n",
       "      <td>A61G</td>\n",
       "      <td>A61G7/0509</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3930274</td>\n",
       "      <td>B</td>\n",
       "      <td>B63</td>\n",
       "      <td>B63B</td>\n",
       "      <td>B63B7/085</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   patent_id section_id subsection_id group_id    subgroup_id\n",
       "0    3930272          A           A47     A47D       A47D7/02\n",
       "1    3930272          Y           Y10     Y10T  Y10T403/32451\n",
       "2    3930273          A           A61     A61G     A61G7/0507\n",
       "3    3930273          A           A61     A61G     A61G7/0509\n",
       "4    3930274          B           B63     B63B      B63B7/085"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# creating \"patents\" table and printing out to csv\n",
    "cpc_current = pd.DataFrame(dfCPC).copy(deep=True)\n",
    "cpc_current = cpc_current.drop(columns=['uuid',\n",
    "                                        'category',\n",
    "                                        'sequence'])\n",
    "cpc_current.to_csv('cpc_current_clean.csv', index=False, sep=',', encoding='utf-8')\n",
    "cpc_current.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## group\n",
    "[acquisitions](#acquisitions)\n",
    " | [application](#application)\n",
    " | [assignee](#assignee) \n",
    " | [cpc_current](#cpc_current)\n",
    " | [group](#group)\n",
    " | [subgroup](#subgroup)\n",
    " | [subsection](#subsection)\n",
    " | [location](#location)\n",
    " | [location_assignee](#location_assignee)\n",
    " | [us_term_of_grant](#us_term_of_grant)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "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>id</th>\n",
       "      <th>title</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A01B</td>\n",
       "      <td>SOIL WORKING IN AGRICULTURE OR FORESTRY; PARTS...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A01C</td>\n",
       "      <td>PLANTING; SOWING; FERTILISING</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A01D</td>\n",
       "      <td>HARVESTING; MOWING</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A01F</td>\n",
       "      <td>PROCESSING OF HARVESTED PRODUCE; HAY OR STRAW ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A01G</td>\n",
       "      <td>HORTICULTURE; CULTIVATION OF VEGETABLES, FLOWE...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     id                                              title\n",
       "0  A01B  SOIL WORKING IN AGRICULTURE OR FORESTRY; PARTS...\n",
       "1  A01C                     PLANTING; SOWING; FERTILISING \n",
       "2  A01D                                HARVESTING; MOWING \n",
       "3  A01F  PROCESSING OF HARVESTED PRODUCE; HAY OR STRAW ...\n",
       "4  A01G  HORTICULTURE; CULTIVATION OF VEGETABLES, FLOWE..."
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# original data\n",
    "dataGroup = pd.read_csv('cpc_group.tsv', sep='\\t', header=0)\n",
    "dfGroup = pd.DataFrame(dataGroup)\n",
    "dfGroup.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "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>id</th>\n",
       "      <th>title</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A01B</td>\n",
       "      <td>SOIL WORKING IN AGRICULTURE OR FORESTRY; PARTS...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A01C</td>\n",
       "      <td>PLANTING; SOWING; FERTILISING</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A01D</td>\n",
       "      <td>HARVESTING; MOWING</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A01F</td>\n",
       "      <td>PROCESSING OF HARVESTED PRODUCE; HAY OR STRAW ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A01G</td>\n",
       "      <td>HORTICULTURE; CULTIVATION OF VEGETABLES, FLOWE...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     id                                              title\n",
       "0  A01B  SOIL WORKING IN AGRICULTURE OR FORESTRY; PARTS...\n",
       "1  A01C                     PLANTING; SOWING; FERTILISING \n",
       "2  A01D                                HARVESTING; MOWING \n",
       "3  A01F  PROCESSING OF HARVESTED PRODUCE; HAY OR STRAW ...\n",
       "4  A01G  HORTICULTURE; CULTIVATION OF VEGETABLES, FLOWE..."
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# clean table and printing out to csv\n",
    "group = pd.DataFrame(dfGroup).copy(deep=True)\n",
    "group.to_csv('group_clean.tsv', index=False, sep='\\t', encoding='utf-8')\n",
    "group.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## subgroup\n",
    "[acquisitions](#acquisitions)\n",
    " | [application](#application)\n",
    " | [assignee](#assignee) \n",
    " | [cpc_current](#cpc_current)\n",
    " | [group](#group)\n",
    " | [subgroup](#subgroup)\n",
    " | [subsection](#subsection)\n",
    " | [location](#location)\n",
    " | [location_assignee](#location_assignee)\n",
    " | [us_term_of_grant](#us_term_of_grant)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "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>id</th>\n",
       "      <th>title</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A01B1/00</td>\n",
       "      <td>Hand tools</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A01B1/02</td>\n",
       "      <td>Hand tools -Spades; Shovels</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A01B1/022</td>\n",
       "      <td>Hand tools -Spades; Shovels -Collapsible; exte...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A01B1/024</td>\n",
       "      <td>Hand tools -Spades; Shovels -Foot protectors a...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A01B1/026</td>\n",
       "      <td>Hand tools -Spades; Shovels -with auxiliary ha...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          id                                              title\n",
       "0   A01B1/00                                         Hand tools\n",
       "1   A01B1/02                        Hand tools -Spades; Shovels\n",
       "2  A01B1/022  Hand tools -Spades; Shovels -Collapsible; exte...\n",
       "3  A01B1/024  Hand tools -Spades; Shovels -Foot protectors a...\n",
       "4  A01B1/026  Hand tools -Spades; Shovels -with auxiliary ha..."
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# original data\n",
    "dataSubgroup = pd.read_csv('cpc_subgroup.tsv', sep='\\t', header=0)\n",
    "dfSubgroup = pd.DataFrame(dataSubgroup)\n",
    "dfSubgroup.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "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>id</th>\n",
       "      <th>title</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A01B1/00</td>\n",
       "      <td>Hand tools</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A01B1/02</td>\n",
       "      <td>Hand tools -Spades; Shovels</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A01B1/022</td>\n",
       "      <td>Hand tools -Spades; Shovels -Collapsible; exte...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A01B1/024</td>\n",
       "      <td>Hand tools -Spades; Shovels -Foot protectors a...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A01B1/026</td>\n",
       "      <td>Hand tools -Spades; Shovels -with auxiliary ha...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          id                                              title\n",
       "0   A01B1/00                                         Hand tools\n",
       "1   A01B1/02                        Hand tools -Spades; Shovels\n",
       "2  A01B1/022  Hand tools -Spades; Shovels -Collapsible; exte...\n",
       "3  A01B1/024  Hand tools -Spades; Shovels -Foot protectors a...\n",
       "4  A01B1/026  Hand tools -Spades; Shovels -with auxiliary ha..."
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# clean table and printing out to csv\n",
    "subgroup = pd.DataFrame(dfSubgroup).copy(deep=True)\n",
    "subgroup.to_csv('subgroup_clean.tsv', index=False, sep='\\t', encoding='utf-8')\n",
    "subgroup.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## subsection\n",
    "[acquisitions](#acquisitions)\n",
    " | [application](#application)\n",
    " | [assignee](#assignee) \n",
    " | [cpc_current](#cpc_current)\n",
    " | [group](#group)\n",
    " | [subgroup](#subgroup)\n",
    " | [subsection](#subsection)\n",
    " | [location](#location)\n",
    " | [location_assignee](#location_assignee)\n",
    " | [us_term_of_grant](#us_term_of_grant)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "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>id</th>\n",
       "      <th>title</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A01</td>\n",
       "      <td>AGRICULTURE; FORESTRY; ANIMAL HUSBANDRY; HUNTI...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A21</td>\n",
       "      <td>BAKING; EDIBLE DOUGHS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A22</td>\n",
       "      <td>BUTCHERING; MEAT TREATMENT; PROCESSING POULTRY...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A23</td>\n",
       "      <td>FOODS OR FOODSTUFFS; THEIR TREATMENT, NOT COVE...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A24</td>\n",
       "      <td>TOBACCO; CIGARS; CIGARETTES; SMOKERS' REQUISITES</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    id                                              title\n",
       "0  A01  AGRICULTURE; FORESTRY; ANIMAL HUSBANDRY; HUNTI...\n",
       "1  A21                              BAKING; EDIBLE DOUGHS\n",
       "2  A22  BUTCHERING; MEAT TREATMENT; PROCESSING POULTRY...\n",
       "3  A23  FOODS OR FOODSTUFFS; THEIR TREATMENT, NOT COVE...\n",
       "4  A24   TOBACCO; CIGARS; CIGARETTES; SMOKERS' REQUISITES"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# original data\n",
    "dataSubsection = pd.read_csv('cpc_subsection.tsv', sep='\\t', header=0)\n",
    "dfSubsection = pd.DataFrame(dataSubsection)\n",
    "dfSubsection.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "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>id</th>\n",
       "      <th>title</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A01</td>\n",
       "      <td>AGRICULTURE; FORESTRY; ANIMAL HUSBANDRY; HUNTI...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A21</td>\n",
       "      <td>BAKING; EDIBLE DOUGHS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A22</td>\n",
       "      <td>BUTCHERING; MEAT TREATMENT; PROCESSING POULTRY...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A23</td>\n",
       "      <td>FOODS OR FOODSTUFFS; THEIR TREATMENT, NOT COVE...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A24</td>\n",
       "      <td>TOBACCO; CIGARS; CIGARETTES; SMOKERS' REQUISITES</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    id                                              title\n",
       "0  A01  AGRICULTURE; FORESTRY; ANIMAL HUSBANDRY; HUNTI...\n",
       "1  A21                              BAKING; EDIBLE DOUGHS\n",
       "2  A22  BUTCHERING; MEAT TREATMENT; PROCESSING POULTRY...\n",
       "3  A23  FOODS OR FOODSTUFFS; THEIR TREATMENT, NOT COVE...\n",
       "4  A24   TOBACCO; CIGARS; CIGARETTES; SMOKERS' REQUISITES"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# clean table and printing out to csv\n",
    "subsection = pd.DataFrame(dfSubsection).copy(deep=True)\n",
    "subsection.to_csv('subsection_clean.tsv', index=False, sep='\\t', encoding='utf-8')\n",
    "\n",
    "subsection.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## location\n",
    "[acquisitions](#acquisitions)\n",
    " | [application](#application)\n",
    " | [assignee](#assignee) \n",
    " | [cpc_current](#cpc_current)\n",
    " | [group](#group)\n",
    " | [subgroup](#subgroup)\n",
    " | [subsection](#subsection)\n",
    " | [location](#location)\n",
    " | [location_assignee](#location_assignee)\n",
    " | [us_term_of_grant](#us_term_of_grant)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "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>id</th>\n",
       "      <th>city</th>\n",
       "      <th>country</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0009wn7out97</td>\n",
       "      <td>Kaiserslautern</td>\n",
       "      <td>DE</td>\n",
       "      <td>49.4123</td>\n",
       "      <td>7.69879</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>000fg4sjgke4</td>\n",
       "      <td>Kiryat Ekron</td>\n",
       "      <td>IL</td>\n",
       "      <td>31.8575</td>\n",
       "      <td>34.82230</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>000wvaottuxr</td>\n",
       "      <td>Oberostendorf</td>\n",
       "      <td>DE</td>\n",
       "      <td>47.9437</td>\n",
       "      <td>10.74330</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>000yomlxfffl</td>\n",
       "      <td>Spruce Pine</td>\n",
       "      <td>US</td>\n",
       "      <td>35.9153</td>\n",
       "      <td>-82.06470</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>000zaps28vbi</td>\n",
       "      <td>Bridge City Westwego</td>\n",
       "      <td>US</td>\n",
       "      <td>29.9179</td>\n",
       "      <td>-90.16630</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             id                  city country latitude  longitude\n",
       "0  0009wn7out97        Kaiserslautern      DE  49.4123    7.69879\n",
       "1  000fg4sjgke4          Kiryat Ekron      IL  31.8575   34.82230\n",
       "2  000wvaottuxr         Oberostendorf      DE  47.9437   10.74330\n",
       "3  000yomlxfffl           Spruce Pine      US  35.9153  -82.06470\n",
       "4  000zaps28vbi  Bridge City Westwego      US  29.9179  -90.16630"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# original data\n",
    "dataLocation = pd.read_csv('location2.tsv', sep=\"\\t\", header=0)\n",
    "dfLocation = pd.DataFrame(dataLocation)\n",
    "dfLocation.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "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>id</th>\n",
       "      <th>city</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>000yomlxfffl</td>\n",
       "      <td>Spruce Pine</td>\n",
       "      <td>35.9153</td>\n",
       "      <td>-82.0647</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>000zaps28vbi</td>\n",
       "      <td>Bridge City Westwego</td>\n",
       "      <td>29.9179</td>\n",
       "      <td>-90.1663</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>002ui5ctlmse</td>\n",
       "      <td>Dahlonega</td>\n",
       "      <td>34.5261</td>\n",
       "      <td>-83.9844</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>0043o8y61p3z</td>\n",
       "      <td>Rancho Bernardo</td>\n",
       "      <td>33.0186</td>\n",
       "      <td>-117.0600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>00a4lw6chrti</td>\n",
       "      <td>Levering</td>\n",
       "      <td>45.6358</td>\n",
       "      <td>-84.7870</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              id                  city latitude  longitude\n",
       "3   000yomlxfffl           Spruce Pine  35.9153   -82.0647\n",
       "4   000zaps28vbi  Bridge City Westwego  29.9179   -90.1663\n",
       "5   002ui5ctlmse             Dahlonega  34.5261   -83.9844\n",
       "7   0043o8y61p3z       Rancho Bernardo  33.0186  -117.0600\n",
       "17  00a4lw6chrti              Levering  45.6358   -84.7870"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# clean table and printing out to csv\n",
    "location = pd.DataFrame(dfLocation).copy(deep=True)\n",
    "location = location[location['country']=='US']\n",
    "location = location.drop(columns=['country'])\n",
    "location.to_csv('location_clean.tsv', index=False, sep='\\t', encoding='utf-8')\n",
    "location.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## location_assignee\n",
    "[acquisitions](#acquisitions)\n",
    " | [application](#application)\n",
    " | [assignee](#assignee) \n",
    " | [cpc_current](#cpc_current)\n",
    " | [group](#group)\n",
    " | [subgroup](#subgroup)\n",
    " | [subsection](#subsection)\n",
    " | [location](#location)\n",
    " | [location_assignee](#location_assignee)\n",
    " | [us_term_of_grant](#us_term_of_grant)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "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>location_id</th>\n",
       "      <th>assignee_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>wy09e6twn0s1</td>\n",
       "      <td>eaa92f175be7bfb71011f17eafb1e71f</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>406gqa22ukdm</td>\n",
       "      <td>e572ad43a89039b0d72acc4ce970a33f</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>lulvs12ykwd7</td>\n",
       "      <td>8ce825a978eebf26ad2c13de6e370bb3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>tdk0ut5vx9ki</td>\n",
       "      <td>6c00cb129070696ef109f6264da00318</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>rfmxwk4iedfc</td>\n",
       "      <td>dabf354c29a6ebba31f54b9ed042241d</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    location_id                       assignee_id\n",
       "0  wy09e6twn0s1  eaa92f175be7bfb71011f17eafb1e71f\n",
       "1  406gqa22ukdm  e572ad43a89039b0d72acc4ce970a33f\n",
       "2  lulvs12ykwd7  8ce825a978eebf26ad2c13de6e370bb3\n",
       "3  tdk0ut5vx9ki  6c00cb129070696ef109f6264da00318\n",
       "4  rfmxwk4iedfc  dabf354c29a6ebba31f54b9ed042241d"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# original data\n",
    "dataLocationAssignee = pd.read_csv('location_assignee.tsv', sep=\"\\t\", header=0)\n",
    "dfLocationAssignee = pd.DataFrame(dataLocationAssignee)\n",
    "dfLocationAssignee.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "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>location_id</th>\n",
       "      <th>assignee_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>wy09e6twn0s1</td>\n",
       "      <td>eaa92f175be7bfb71011f17eafb1e71f</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>406gqa22ukdm</td>\n",
       "      <td>e572ad43a89039b0d72acc4ce970a33f</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>lulvs12ykwd7</td>\n",
       "      <td>8ce825a978eebf26ad2c13de6e370bb3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>tdk0ut5vx9ki</td>\n",
       "      <td>6c00cb129070696ef109f6264da00318</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>rfmxwk4iedfc</td>\n",
       "      <td>dabf354c29a6ebba31f54b9ed042241d</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    location_id                       assignee_id\n",
       "0  wy09e6twn0s1  eaa92f175be7bfb71011f17eafb1e71f\n",
       "1  406gqa22ukdm  e572ad43a89039b0d72acc4ce970a33f\n",
       "2  lulvs12ykwd7  8ce825a978eebf26ad2c13de6e370bb3\n",
       "3  tdk0ut5vx9ki  6c00cb129070696ef109f6264da00318\n",
       "4  rfmxwk4iedfc  dabf354c29a6ebba31f54b9ed042241d"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# clean table and printing out to csv\n",
    "location_assignee = pd.DataFrame(dfLocationAssignee).copy(deep=True)\n",
    "location_assignee.to_csv('location_assignee_clean.csv', index=False, sep=',', encoding='utf-8')\n",
    "location_assignee.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## us_term_of_grant\n",
    "[acquisitions](#acquisitions)\n",
    " | [application](#application)\n",
    " | [assignee](#assignee) \n",
    " | [cpc_current](#cpc_current)\n",
    " | [group](#group)\n",
    " | [subgroup](#subgroup)\n",
    " | [subsection](#subsection)\n",
    " | [location](#location)\n",
    " | [location_assignee](#location_assignee)\n",
    " | [us_term_of_grant](#us_term_of_grant)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "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>uuid</th>\n",
       "      <th>patent_id</th>\n",
       "      <th>lapse_of_patent</th>\n",
       "      <th>disclaimer_date</th>\n",
       "      <th>term_disclaimer</th>\n",
       "      <th>term_grant</th>\n",
       "      <th>term_extension</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0000hq4800wcm85ghvyz34mbe</td>\n",
       "      <td>D657425</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0000-00-00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0002e24jgnd6uspokbvss6xex</td>\n",
       "      <td>D699845</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0000-00-00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0002s1xi2xuinyor7g5w5veu6</td>\n",
       "      <td>D525308</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0000-00-00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>000315mx91ci258k7nwyve9pg</td>\n",
       "      <td>9193114</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0000-00-00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>408.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0003hd31mgyh3f2x7j8stmuq6</td>\n",
       "      <td>D532925</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0000-00-00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        uuid patent_id  lapse_of_patent disclaimer_date  \\\n",
       "0  0000hq4800wcm85ghvyz34mbe   D657425              NaN      0000-00-00   \n",
       "1  0002e24jgnd6uspokbvss6xex   D699845              NaN      0000-00-00   \n",
       "2  0002s1xi2xuinyor7g5w5veu6   D525308              NaN      0000-00-00   \n",
       "3  000315mx91ci258k7nwyve9pg   9193114              NaN      0000-00-00   \n",
       "4  0003hd31mgyh3f2x7j8stmuq6   D532925              NaN      0000-00-00   \n",
       "\n",
       "  term_disclaimer  term_grant  term_extension  \n",
       "0             NaN        14.0             NaN  \n",
       "1             NaN        14.0             NaN  \n",
       "2             NaN        14.0             NaN  \n",
       "3             NaN         NaN           408.0  \n",
       "4             NaN        14.0             NaN  "
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dataExpired = pd.read_csv('us_term_of_grant.tsv', sep=\"\\t\", header=0)\n",
    "dfExpired = pd.DataFrame(dataExpired)\n",
    "dfExpired.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "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>patent_id</th>\n",
       "      <th>disclaimer_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>D657425</td>\n",
       "      <td>0000-00-00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>D699845</td>\n",
       "      <td>0000-00-00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>D525308</td>\n",
       "      <td>0000-00-00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>9193114</td>\n",
       "      <td>0000-00-00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>D532925</td>\n",
       "      <td>0000-00-00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  patent_id disclaimer_date\n",
       "0   D657425      0000-00-00\n",
       "1   D699845      0000-00-00\n",
       "2   D525308      0000-00-00\n",
       "3   9193114      0000-00-00\n",
       "4   D532925      0000-00-00"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# clean table and printing out to csv\n",
    "us_term_of_grant = pd.DataFrame(dfExpired).copy(deep=True)\n",
    "us_term_of_grant = us_term_of_grant.drop(columns=['uuid',\n",
    "                                                  'lapse_of_patent',\n",
    "                                                  'term_disclaimer',\n",
    "                                                  'term_grant',\n",
    "                                                  'term_extension'])\n",
    "us_term_of_grant.to_csv('us_term_of_grant_clean.csv', index=False, sep=',', encoding='utf-8')\n",
    "us_term_of_grant.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "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>uuid</th>\n",
       "      <th>patent_id</th>\n",
       "      <th>lapse_of_patent</th>\n",
       "      <th>disclaimer_date</th>\n",
       "      <th>term_disclaimer</th>\n",
       "      <th>term_grant</th>\n",
       "      <th>term_extension</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>164</th>\n",
       "      <td>002aq634gkvojyzmxlz8g0wno</td>\n",
       "      <td>D334422</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2005-03-12</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>172</th>\n",
       "      <td>002cmto548ezdiejy8i2a28qq</td>\n",
       "      <td>5089490</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2005-07-19</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>182</th>\n",
       "      <td>002h7kmntnf61xhsn3mcrj4al</td>\n",
       "      <td>4805394</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2002-11-21</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>249</th>\n",
       "      <td>003gr6ne1h4286un48q7mr0b1</td>\n",
       "      <td>D387548</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2011-11-11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>268</th>\n",
       "      <td>003omfcsvfmp4lvqyymew3ai0</td>\n",
       "      <td>4591481</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2001-02-14</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>313</th>\n",
       "      <td>004dvl0nxz3kwgzs5qe4fazf5</td>\n",
       "      <td>5216281</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2008-08-27</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>385</th>\n",
       "      <td>005gbradp0hvjubfcfz9tjq3k</td>\n",
       "      <td>4764874</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2005-05-03</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>514</th>\n",
       "      <td>007gbj8mt28y1lxo5cvjygjy8</td>\n",
       "      <td>5229026</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2008-10-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>606</th>\n",
       "      <td>008rm9t62n6as0bjthe31xgay</td>\n",
       "      <td>D360500</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2009-06-20</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>674</th>\n",
       "      <td>009s40sos1n7ikkqzfrq7i3zx</td>\n",
       "      <td>5468151</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2011-07-19</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>710</th>\n",
       "      <td>00adxq7jvbogo2fecbvzgmydj</td>\n",
       "      <td>4472514</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2011-09-18</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>937</th>\n",
       "      <td>00dexq85jha3gwwumof6kajh4</td>\n",
       "      <td>5066547</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2005-10-18</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1064</th>\n",
       "      <td>00fedvgen1ytz7b2yagbf8oq1</td>\n",
       "      <td>5176332</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2007-12-04</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1086</th>\n",
       "      <td>00frxe466b3lbnqhzb6ttiqpn</td>\n",
       "      <td>5149350</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2008-01-22</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1124</th>\n",
       "      <td>00g3xjc9ahl77yo1ebex5o7p3</td>\n",
       "      <td>4426297</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1998-10-06</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1165</th>\n",
       "      <td>00gnewspkfms4ul5s0f694y1x</td>\n",
       "      <td>4103048</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1994-06-21</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1186</th>\n",
       "      <td>00h0dc4dr6f6sfo2oaqh4fz0r</td>\n",
       "      <td>4398835</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1998-11-10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1237</th>\n",
       "      <td>00hoymc6115rabb48zh0i9ziv</td>\n",
       "      <td>4019826</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1989-10-24</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1261</th>\n",
       "      <td>00i22q65f4xjp6ct6ueds5q9p</td>\n",
       "      <td>5147300</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2008-05-28</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1282</th>\n",
       "      <td>00iexqs0v07f2juapop1b2zwe</td>\n",
       "      <td>4347649</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1997-07-29</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1362</th>\n",
       "      <td>00jrfzex0fzf8cvojg7pe2bp0</td>\n",
       "      <td>4894282</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2004-07-28</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1445</th>\n",
       "      <td>00kxff09ulgw6dlah8kqosd2b</td>\n",
       "      <td>5358122</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2010-04-27</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1449</th>\n",
       "      <td>00kzg783b7qxnw6xpmexj1h0y</td>\n",
       "      <td>4205134</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1995-12-19</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1474</th>\n",
       "      <td>00lbmsrj2pbb406v6ej2j879n</td>\n",
       "      <td>4282598</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1993-03-16</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1516</th>\n",
       "      <td>00lxty2j0rnk0leijn0ux56vg</td>\n",
       "      <td>4594960</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2001-11-06</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1518</th>\n",
       "      <td>00lzl6hfrf98bmzz8agx7qr1f</td>\n",
       "      <td>5102867</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2006-07-18</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1519</th>\n",
       "      <td>00m0qs67u00xwj179rjqhhhoq</td>\n",
       "      <td>5314694</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2009-06-16</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1544</th>\n",
       "      <td>00mcl113ly51kg7xmfa0upq30</td>\n",
       "      <td>4935632</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2005-11-29</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1595</th>\n",
       "      <td>00n6762gej28oevp9ughewjad</td>\n",
       "      <td>5078137</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2005-05-03</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1615</th>\n",
       "      <td>00nh4wmz65xau7jenljornhxu</td>\n",
       "      <td>D382258</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2010-11-26</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1618</th>\n",
       "      <td>00nho8nb57jzde09tjsezhc0c</td>\n",
       "      <td>4505143</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1999-12-21</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1831</th>\n",
       "      <td>00qscuh49bawvk0k6v2bw6mqz</td>\n",
       "      <td>5147326</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2009-02-18</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1848</th>\n",
       "      <td>00qxvwyxhxkfe4k3xohchinp5</td>\n",
       "      <td>4752376</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2003-05-27</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1882</th>\n",
       "      <td>00ret9vb78ntj3z7w8wjgy5gz</td>\n",
       "      <td>5321913</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2009-01-21</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1961</th>\n",
       "      <td>00slabffzxedukghptt9f8ul4</td>\n",
       "      <td>4361498</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1997-11-25</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022</th>\n",
       "      <td>00tk86j0wl4hwwwqpx7sj46ye</td>\n",
       "      <td>5266200</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2007-04-17</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2039</th>\n",
       "      <td>00tp7jiqgrn4hp5ksjgnmrig3</td>\n",
       "      <td>5153254</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2003-10-21</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2052</th>\n",
       "      <td>00tvc3bde9wonmrhvax8tdsp8</td>\n",
       "      <td>4825124</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2004-12-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2110</th>\n",
       "      <td>00upayynfobpgv5px6eipzs23</td>\n",
       "      <td>D347814</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2008-05-10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2113</th>\n",
       "      <td>00uqnj9n15zim5tlxzl65bh3k</td>\n",
       "      <td>5511762</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2013-11-15</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2161</th>\n",
       "      <td>00vczotqzizk6rmdr6wbhruen</td>\n",
       "      <td>5315717</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2010-06-08</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2186</th>\n",
       "      <td>00vqhcmfzzm04n2w986t766q7</td>\n",
       "      <td>5880317</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2011-05-24</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2232</th>\n",
       "      <td>00wgd1559c5mf40r5tn7mskbd</td>\n",
       "      <td>D338721</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2007-06-29</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2450</th>\n",
       "      <td>00zqj2niv4d4oiluccme63aki</td>\n",
       "      <td>5364903</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2010-02-09</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2453</th>\n",
       "      <td>00zrhvhcsm8tawqi30lsasgwk</td>\n",
       "      <td>4351381</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1994-01-22</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2564</th>\n",
       "      <td>0115deglh1eifyanqcc41d5bg</td>\n",
       "      <td>4990622</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2007-09-18</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2571</th>\n",
       "      <td>0119frscux4iv0zozlvhwcirk</td>\n",
       "      <td>5264001</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2007-10-09</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2582</th>\n",
       "      <td>011hpdeco22m4k1ou60aqrrro</td>\n",
       "      <td>4388109</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1998-12-22</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2596</th>\n",
       "      <td>011qwsm5c2cc13jsjzvur2i31</td>\n",
       "      <td>5448678</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2011-05-24</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2711</th>\n",
       "      <td>013ingv9fgsaz4vtg37qf28qv</td>\n",
       "      <td>4022843</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1993-09-07</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           uuid patent_id  lapse_of_patent disclaimer_date  \\\n",
       "164   002aq634gkvojyzmxlz8g0wno   D334422              NaN      2005-03-12   \n",
       "172   002cmto548ezdiejy8i2a28qq   5089490              NaN      2005-07-19   \n",
       "182   002h7kmntnf61xhsn3mcrj4al   4805394              NaN      2002-11-21   \n",
       "249   003gr6ne1h4286un48q7mr0b1   D387548              NaN      2011-11-11   \n",
       "268   003omfcsvfmp4lvqyymew3ai0   4591481              NaN      2001-02-14   \n",
       "313   004dvl0nxz3kwgzs5qe4fazf5   5216281              NaN      2008-08-27   \n",
       "385   005gbradp0hvjubfcfz9tjq3k   4764874              NaN      2005-05-03   \n",
       "514   007gbj8mt28y1lxo5cvjygjy8   5229026              NaN      2008-10-01   \n",
       "606   008rm9t62n6as0bjthe31xgay   D360500              NaN      2009-06-20   \n",
       "674   009s40sos1n7ikkqzfrq7i3zx   5468151              NaN      2011-07-19   \n",
       "710   00adxq7jvbogo2fecbvzgmydj   4472514              NaN      2011-09-18   \n",
       "937   00dexq85jha3gwwumof6kajh4   5066547              NaN      2005-10-18   \n",
       "1064  00fedvgen1ytz7b2yagbf8oq1   5176332              NaN      2007-12-04   \n",
       "1086  00frxe466b3lbnqhzb6ttiqpn   5149350              NaN      2008-01-22   \n",
       "1124  00g3xjc9ahl77yo1ebex5o7p3   4426297              NaN      1998-10-06   \n",
       "1165  00gnewspkfms4ul5s0f694y1x   4103048              NaN      1994-06-21   \n",
       "1186  00h0dc4dr6f6sfo2oaqh4fz0r   4398835              NaN      1998-11-10   \n",
       "1237  00hoymc6115rabb48zh0i9ziv   4019826              NaN      1989-10-24   \n",
       "1261  00i22q65f4xjp6ct6ueds5q9p   5147300              NaN      2008-05-28   \n",
       "1282  00iexqs0v07f2juapop1b2zwe   4347649              NaN      1997-07-29   \n",
       "1362  00jrfzex0fzf8cvojg7pe2bp0   4894282              NaN      2004-07-28   \n",
       "1445  00kxff09ulgw6dlah8kqosd2b   5358122              NaN      2010-04-27   \n",
       "1449  00kzg783b7qxnw6xpmexj1h0y   4205134              NaN      1995-12-19   \n",
       "1474  00lbmsrj2pbb406v6ej2j879n   4282598              NaN      1993-03-16   \n",
       "1516  00lxty2j0rnk0leijn0ux56vg   4594960              NaN      2001-11-06   \n",
       "1518  00lzl6hfrf98bmzz8agx7qr1f   5102867              NaN      2006-07-18   \n",
       "1519  00m0qs67u00xwj179rjqhhhoq   5314694              NaN      2009-06-16   \n",
       "1544  00mcl113ly51kg7xmfa0upq30   4935632              NaN      2005-11-29   \n",
       "1595  00n6762gej28oevp9ughewjad   5078137              NaN      2005-05-03   \n",
       "1615  00nh4wmz65xau7jenljornhxu   D382258              NaN      2010-11-26   \n",
       "1618  00nho8nb57jzde09tjsezhc0c   4505143              NaN      1999-12-21   \n",
       "1831  00qscuh49bawvk0k6v2bw6mqz   5147326              NaN      2009-02-18   \n",
       "1848  00qxvwyxhxkfe4k3xohchinp5   4752376              NaN      2003-05-27   \n",
       "1882  00ret9vb78ntj3z7w8wjgy5gz   5321913              NaN      2009-01-21   \n",
       "1961  00slabffzxedukghptt9f8ul4   4361498              NaN      1997-11-25   \n",
       "2022  00tk86j0wl4hwwwqpx7sj46ye   5266200              NaN      2007-04-17   \n",
       "2039  00tp7jiqgrn4hp5ksjgnmrig3   5153254              NaN      2003-10-21   \n",
       "2052  00tvc3bde9wonmrhvax8tdsp8   4825124              NaN      2004-12-01   \n",
       "2110  00upayynfobpgv5px6eipzs23   D347814              NaN      2008-05-10   \n",
       "2113  00uqnj9n15zim5tlxzl65bh3k   5511762              NaN      2013-11-15   \n",
       "2161  00vczotqzizk6rmdr6wbhruen   5315717              NaN      2010-06-08   \n",
       "2186  00vqhcmfzzm04n2w986t766q7   5880317              NaN      2011-05-24   \n",
       "2232  00wgd1559c5mf40r5tn7mskbd   D338721              NaN      2007-06-29   \n",
       "2450  00zqj2niv4d4oiluccme63aki   5364903              NaN      2010-02-09   \n",
       "2453  00zrhvhcsm8tawqi30lsasgwk   4351381              NaN      1994-01-22   \n",
       "2564  0115deglh1eifyanqcc41d5bg   4990622              NaN      2007-09-18   \n",
       "2571  0119frscux4iv0zozlvhwcirk   5264001              NaN      2007-10-09   \n",
       "2582  011hpdeco22m4k1ou60aqrrro   4388109              NaN      1998-12-22   \n",
       "2596  011qwsm5c2cc13jsjzvur2i31   5448678              NaN      2011-05-24   \n",
       "2711  013ingv9fgsaz4vtg37qf28qv   4022843              NaN      1993-09-07   \n",
       "\n",
       "     term_disclaimer  term_grant  term_extension  \n",
       "164              NaN        14.0             NaN  \n",
       "172              NaN         NaN             NaN  \n",
       "182              NaN         NaN             NaN  \n",
       "249              NaN        14.0             NaN  \n",
       "268              NaN         NaN             NaN  \n",
       "313              NaN         NaN             NaN  \n",
       "385              NaN         NaN             NaN  \n",
       "514              NaN         NaN             NaN  \n",
       "606              NaN        14.0             NaN  \n",
       "674              NaN         NaN             NaN  \n",
       "710              NaN         NaN             NaN  \n",
       "937              NaN         NaN             NaN  \n",
       "1064             NaN         NaN             NaN  \n",
       "1086             NaN         NaN             NaN  \n",
       "1124             NaN         NaN             NaN  \n",
       "1165             NaN         NaN             NaN  \n",
       "1186             NaN         NaN             NaN  \n",
       "1237             NaN         NaN             NaN  \n",
       "1261             NaN         NaN             NaN  \n",
       "1282             NaN         NaN             NaN  \n",
       "1362             NaN         NaN             NaN  \n",
       "1445             NaN         NaN             NaN  \n",
       "1449             NaN         NaN             NaN  \n",
       "1474             NaN         NaN             NaN  \n",
       "1516             NaN         NaN             NaN  \n",
       "1518             NaN         NaN             NaN  \n",
       "1519             NaN         NaN             NaN  \n",
       "1544             NaN         NaN             NaN  \n",
       "1595             NaN         NaN             NaN  \n",
       "1615             NaN        14.0             NaN  \n",
       "1618             NaN         NaN             NaN  \n",
       "1831             NaN         NaN             NaN  \n",
       "1848             NaN         NaN             NaN  \n",
       "1882             NaN         NaN             NaN  \n",
       "1961             NaN         NaN             NaN  \n",
       "2022             NaN         NaN             NaN  \n",
       "2039             NaN         NaN             NaN  \n",
       "2052             NaN         NaN             NaN  \n",
       "2110             NaN        14.0             NaN  \n",
       "2113             NaN         NaN             NaN  \n",
       "2161             NaN         NaN             NaN  \n",
       "2186             NaN         NaN             NaN  \n",
       "2232             NaN        14.0             NaN  \n",
       "2450             NaN         NaN             NaN  \n",
       "2453             NaN         NaN             NaN  \n",
       "2564             NaN         NaN             NaN  \n",
       "2571             NaN         NaN             NaN  \n",
       "2582             NaN         NaN             NaN  \n",
       "2596             NaN         NaN             NaN  \n",
       "2711             NaN         NaN             NaN  "
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# dfExpired[dfExpired['disclaimer_date']!='0000-00-00'].head(50)"
   ]
  }
 ],
 "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.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}