3 - Company Ownership.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Collect a Company's Patent Porfolio\n",
    "\n",
    "This notebook will attempt to amass, for any given company, their entire worldwide patent porfolio.\\*\n",
    "\n",
    "### U.S. Portfolio\n",
    "\n",
    "Collecting their U.S. portfolio will proceed in a few steps:\n",
    "\n",
    "1. Collect all applications ever assigned to the company\n",
    "\n",
    "    a. Those applications naming the company as an applicant\n",
    "    b. Those assigned to the company via an assignment\n",
    "    \n",
    "2. Prune all applications assigned out of the company, as recorded in USPTO assignment records\n",
    "\n",
    "\\*NOTE: I say \"attempt\" because the notebook relies on the USPTO system, and the International Patent Document (INPADOC) service. Not all foreign jurisdictions participate in INPADOC, and not all INPADOC member states are as good at contributing to the system as we would like. So coverage is good, but not perfect."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First, we do our standard imports, and grab the USApplication and Assignment objects from patent_client"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from patent_client import USApplication, Assignment\n",
    "\n",
    "company_name = 'Tesla Motors'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 1.a: Collect all applications naming the company as applicant"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "applicant_apps = USApplication.objects.filter(first_named_applicant=company_name).values_list('appl_id', flat=True).to_set()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 1.b: Collect all applications assigned to the company"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# The assigned apps is either a single value, or a list of values if more than one property was assigned\n",
    "assigned_apps = Assignment.objects.filter(assignee=company_name).explode('properties').values_list('appl_id', flat=True).to_set()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And now we combine the two"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total U.S. Applications Ever Owned by Tesla Motors: 502\n"
     ]
    }
   ],
   "source": [
    "all_apps = applicant_apps | assigned_apps\n",
    "\n",
    "print(f'Total U.S. Applications Ever Owned by {company_name}: {len(all_apps)}')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 2: Collect all applications ever assigned out of the company"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "U.S. Applications assigned out of Tesla Motors: 4\n"
     ]
    }
   ],
   "source": [
    "assigned_out_apps = set(Assignment.objects.filter(assignor=company_name).to_pandas()\n",
    "                   .query('conveyance_text == \"ASSIGNMENT OF ASSIGNORS INTEREST\"')\n",
    "                   .properties.explode().apply(lambda x: x.appl_id).to_list())\n",
    "print(f'U.S. Applications assigned out of {company_name}: {len(assigned_out_apps)}')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 3: Subtract one set from the other"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "498"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "owned_apps = list(set(all_apps) - set(assigned_out_apps))\n",
    "len(owned_apps)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 4: Generate status report"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>appl_id</th>\n",
       "      <th>app_filing_date</th>\n",
       "      <th>app_early_pub_number</th>\n",
       "      <th>app_early_pub_date</th>\n",
       "      <th>patent_number</th>\n",
       "      <th>patent_issue_date</th>\n",
       "      <th>patent_title</th>\n",
       "      <th>inventors__0__name</th>\n",
       "      <th>applicants__0__name</th>\n",
       "      <th>app_status</th>\n",
       "      <th>app_status_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>15300044</td>\n",
       "      <td>2016-09-28</td>\n",
       "      <td>US20170177048A1</td>\n",
       "      <td>2017-06-22</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>FUNCTIONAL REDUNDANCY OF COMMUNICATIONS AND DA...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Response to Non-Final Office Action Entered an...</td>\n",
       "      <td>2020-02-22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>15632587</td>\n",
       "      <td>2017-06-26</td>\n",
       "      <td>US20170291482A1</td>\n",
       "      <td>2017-10-12</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>INTEGRATED ELECTRIC MOTOR ASSEMBLY</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Response after Final Action Forwarded to Examiner</td>\n",
       "      <td>2020-02-14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>16202865</td>\n",
       "      <td>2018-11-28</td>\n",
       "      <td>US20190097508A1</td>\n",
       "      <td>2019-03-28</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>GEOMETRY OF ROTOR END RING AND STATOR END TURNS</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Response to Non-Final Office Action Entered an...</td>\n",
       "      <td>2020-02-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>16382614</td>\n",
       "      <td>2019-04-12</td>\n",
       "      <td>US20190232344A1</td>\n",
       "      <td>2019-08-01</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>CLOSED-LOOP THERMAL SERVICING OF SOLVENT-REFIN...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Response to Non-Final Office Action Entered an...</td>\n",
       "      <td>2020-02-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>15849831</td>\n",
       "      <td>2017-12-21</td>\n",
       "      <td>US20180111576A1</td>\n",
       "      <td>2018-04-26</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>LOAD LIMITER CONFIGURATION USING MULTIDIMENSIO...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Notice of Allowance Mailed -- Application Rece...</td>\n",
       "      <td>2020-01-27</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    appl_id app_filing_date app_early_pub_number app_early_pub_date  \\\n",
       "0  15300044      2016-09-28      US20170177048A1         2017-06-22   \n",
       "1  15632587      2017-06-26      US20170291482A1         2017-10-12   \n",
       "2  16202865      2018-11-28      US20190097508A1         2019-03-28   \n",
       "3  16382614      2019-04-12      US20190232344A1         2019-08-01   \n",
       "4  15849831      2017-12-21      US20180111576A1         2018-04-26   \n",
       "\n",
       "  patent_number patent_issue_date  \\\n",
       "0          None              None   \n",
       "1          None              None   \n",
       "2          None              None   \n",
       "3          None              None   \n",
       "4          None              None   \n",
       "\n",
       "                                        patent_title inventors__0__name  \\\n",
       "0  FUNCTIONAL REDUNDANCY OF COMMUNICATIONS AND DA...               None   \n",
       "1                 INTEGRATED ELECTRIC MOTOR ASSEMBLY               None   \n",
       "2    GEOMETRY OF ROTOR END RING AND STATOR END TURNS               None   \n",
       "3  CLOSED-LOOP THERMAL SERVICING OF SOLVENT-REFIN...               None   \n",
       "4  LOAD LIMITER CONFIGURATION USING MULTIDIMENSIO...               None   \n",
       "\n",
       "  applicants__0__name                                         app_status  \\\n",
       "0                None  Response to Non-Final Office Action Entered an...   \n",
       "1                None  Response after Final Action Forwarded to Examiner   \n",
       "2                None  Response to Non-Final Office Action Entered an...   \n",
       "3                None  Response to Non-Final Office Action Entered an...   \n",
       "4                None  Notice of Allowance Mailed -- Application Rece...   \n",
       "\n",
       "  app_status_date  \n",
       "0      2020-02-22  \n",
       "1      2020-02-14  \n",
       "2      2020-02-09  \n",
       "3      2020-02-03  \n",
       "4      2020-01-27  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "from patent_client.parser import parse\n",
    "\n",
    "us_df = USApplication.objects.filter(appl_id=owned_apps).values(\n",
    "    'appl_id', \n",
    "    'app_filing_date', \n",
    "    'app_early_pub_number',\n",
    "    'app_early_pub_date',\n",
    "    'patent_number',\n",
    "    'patent_issue_date',\n",
    "    'patent_title',\n",
    "    'inventors__0__name',\n",
    "    'applicants__0__name',\n",
    "    'app_status',\n",
    "    'app_status_date'\n",
    ").to_pandas()\n",
    "\n",
    "us_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Foreign Patent Portfolio\n",
    "\n",
    "Now we will fetch the company's wordwide portfolio, as it appears in the Inpadoc system maintained by the EPO"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "from patent_client_plus import Inpadoc\n",
    "\n",
    "foreign = Inpadoc.objects.filter(applicant=company_name)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And now, a status report:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>country</th>\n",
       "      <th>appl_id</th>\n",
       "      <th>app_filing_date</th>\n",
       "      <th>pub_number</th>\n",
       "      <th>pub_date</th>\n",
       "      <th>title</th>\n",
       "      <th>applicant</th>\n",
       "      <th>first_named_inventor</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>CA</td>\n",
       "      <td>3071314</td>\n",
       "      <td>None</td>\n",
       "      <td>3071314</td>\n",
       "      <td>2019-02-07</td>\n",
       "      <td>NOVEL BATTERY SYSTEMS BASED ON LITHIUM DIFLUOR...</td>\n",
       "      <td>TESLA MOTORS CANADA ULC</td>\n",
       "      <td>MA, LIN,</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>CA</td>\n",
       "      <td>3013743</td>\n",
       "      <td>None</td>\n",
       "      <td>3013743</td>\n",
       "      <td>2019-12-20</td>\n",
       "      <td>DIOXAZOLONES AND NITRILE SULFITES AS ELECTROLY...</td>\n",
       "      <td>TESLA MOTORS CANADA ULC</td>\n",
       "      <td>DAHN, JEFFERY RAYMOND,</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>WO</td>\n",
       "      <td>2018000162</td>\n",
       "      <td>None</td>\n",
       "      <td>2019241869</td>\n",
       "      <td>2019-12-26</td>\n",
       "      <td>DIOXAZOLONES AND NITRILE SULFITES AS ELECTROLY...</td>\n",
       "      <td>TESLA MOTORS CANADA ULC</td>\n",
       "      <td>DAHN, Jeffery,</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>CA</td>\n",
       "      <td>3013596</td>\n",
       "      <td>None</td>\n",
       "      <td>3013596</td>\n",
       "      <td>2019-09-12</td>\n",
       "      <td>NOVEL BATTERY SYSTEMS BASED ON TWO-ADDITIVE EL...</td>\n",
       "      <td>TESLA MOTORS CANADA ULC</td>\n",
       "      <td>DAHN, JEFFERY RAYMOND,</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>WO</td>\n",
       "      <td>2018000164</td>\n",
       "      <td>None</td>\n",
       "      <td>2019173892</td>\n",
       "      <td>2019-09-19</td>\n",
       "      <td>NOVEL BATTERY SYSTEMS BASED ON TWO-ADDITIVE EL...</td>\n",
       "      <td>TESLA MOTORS CANADA ULC</td>\n",
       "      <td>DAHN, Jeffery Raymond,</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>WO</td>\n",
       "      <td>2018000163</td>\n",
       "      <td>None</td>\n",
       "      <td>2019173891</td>\n",
       "      <td>2019-09-19</td>\n",
       "      <td>NOVEL BATTERY SYSTEMS BASED ON TWO-ADDITIVE EL...</td>\n",
       "      <td>TESLA MOTORS CANADA ULC</td>\n",
       "      <td>DAHN, Jeffery,</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>CN</td>\n",
       "      <td>201780073009</td>\n",
       "      <td>None</td>\n",
       "      <td>110073170</td>\n",
       "      <td>2019-07-30</td>\n",
       "      <td>OBSCURING, COLOR MATCHING, AND CAMOUFLAGING SO...</td>\n",
       "      <td>特斯拉公司</td>\n",
       "      <td>迈克尔·皮里奥德,</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>WO</td>\n",
       "      <td>2018051371</td>\n",
       "      <td>None</td>\n",
       "      <td>2019109167</td>\n",
       "      <td>2019-06-13</td>\n",
       "      <td>METHOD AND SYSTEM FOR DETERMINING CONCENTRATIO...</td>\n",
       "      <td>TESLA MOTORS CANADA ULC</td>\n",
       "      <td>ELLIS, Leah Devorah,</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>CA</td>\n",
       "      <td>2992228</td>\n",
       "      <td>None</td>\n",
       "      <td>2992228</td>\n",
       "      <td>2019-06-06</td>\n",
       "      <td>METHOD AND SYSTEM FOR DETERMINING CONCENTRATIO...</td>\n",
       "      <td>TESLA MOTORS CANADA ULC</td>\n",
       "      <td>ELLIS, LEAH DEVORAH,</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>WO</td>\n",
       "      <td>2018055745</td>\n",
       "      <td>None</td>\n",
       "      <td>2019025980</td>\n",
       "      <td>2019-02-07</td>\n",
       "      <td>NOVEL BATTERY SYSTEMS BASED ON LITHIUM DIFLUOR...</td>\n",
       "      <td>TESLA MOTORS CANADA ULC</td>\n",
       "      <td>MA, Lin,</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>CA</td>\n",
       "      <td>2992224</td>\n",
       "      <td>None</td>\n",
       "      <td>2992224</td>\n",
       "      <td>2019-01-31</td>\n",
       "      <td>NOVEL BATTERY SYSTEMS BASED ON TWO-ADDITIVE EL...</td>\n",
       "      <td>TESLA MOTORS CANADA ULC</td>\n",
       "      <td>DAHN, JEFFERY R,</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   country       appl_id app_filing_date  pub_number    pub_date  \\\n",
       "0       CA       3071314            None     3071314  2019-02-07   \n",
       "1       CA       3013743            None     3013743  2019-12-20   \n",
       "2       WO    2018000162            None  2019241869  2019-12-26   \n",
       "4       CA       3013596            None     3013596  2019-09-12   \n",
       "5       WO    2018000164            None  2019173892  2019-09-19   \n",
       "6       WO    2018000163            None  2019173891  2019-09-19   \n",
       "9       CN  201780073009            None   110073170  2019-07-30   \n",
       "10      WO    2018051371            None  2019109167  2019-06-13   \n",
       "11      CA       2992228            None     2992228  2019-06-06   \n",
       "13      WO    2018055745            None  2019025980  2019-02-07   \n",
       "14      CA       2992224            None     2992224  2019-01-31   \n",
       "\n",
       "                                                title  \\\n",
       "0   NOVEL BATTERY SYSTEMS BASED ON LITHIUM DIFLUOR...   \n",
       "1   DIOXAZOLONES AND NITRILE SULFITES AS ELECTROLY...   \n",
       "2   DIOXAZOLONES AND NITRILE SULFITES AS ELECTROLY...   \n",
       "4   NOVEL BATTERY SYSTEMS BASED ON TWO-ADDITIVE EL...   \n",
       "5   NOVEL BATTERY SYSTEMS BASED ON TWO-ADDITIVE EL...   \n",
       "6   NOVEL BATTERY SYSTEMS BASED ON TWO-ADDITIVE EL...   \n",
       "9   OBSCURING, COLOR MATCHING, AND CAMOUFLAGING SO...   \n",
       "10  METHOD AND SYSTEM FOR DETERMINING CONCENTRATIO...   \n",
       "11  METHOD AND SYSTEM FOR DETERMINING CONCENTRATIO...   \n",
       "13  NOVEL BATTERY SYSTEMS BASED ON LITHIUM DIFLUOR...   \n",
       "14  NOVEL BATTERY SYSTEMS BASED ON TWO-ADDITIVE EL...   \n",
       "\n",
       "                  applicant    first_named_inventor  \n",
       "0   TESLA MOTORS CANADA ULC                MA, LIN,  \n",
       "1   TESLA MOTORS CANADA ULC  DAHN, JEFFERY RAYMOND,  \n",
       "2   TESLA MOTORS CANADA ULC          DAHN, Jeffery,  \n",
       "4   TESLA MOTORS CANADA ULC  DAHN, JEFFERY RAYMOND,  \n",
       "5   TESLA MOTORS CANADA ULC  DAHN, Jeffery Raymond,  \n",
       "6   TESLA MOTORS CANADA ULC          DAHN, Jeffery,  \n",
       "9                     特斯拉公司               迈克尔·皮里奥德,  \n",
       "10  TESLA MOTORS CANADA ULC    ELLIS, Leah Devorah,  \n",
       "11  TESLA MOTORS CANADA ULC    ELLIS, LEAH DEVORAH,  \n",
       "13  TESLA MOTORS CANADA ULC                MA, Lin,  \n",
       "14  TESLA MOTORS CANADA ULC        DAHN, JEFFERY R,  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "foreign_df = foreign[:15].values(\n",
    "    'country', \n",
    "    appl_id='biblio.applications.0.number', \n",
    "    app_filing_date='biblio.applications.0.date', \n",
    "    pub_number='biblio.publications.0.number', \n",
    "    pub_date='biblio.publications.0.date', \n",
    "    title='biblio.title', \n",
    "    applicant='biblio.applicants.0', \n",
    "    first_named_inventor='biblio.inventors.0').to_pandas()\n",
    "foreign_df = foreign_df[foreign_df['country'] != 'US']\n",
    "foreign_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And now we will take our results, and write to an excel file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "writer = pd.ExcelWriter(f'{company_name}_portfolio.xlsx')\n",
    "us_df.to_excel(writer, sheet_name='US Portfolio')\n",
    "foreign_df.to_excel(writer, sheet_name='Foreign Portfolio')\n",
    "writer.save()"
   ]
  }
 ],
 "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.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}