feature-selection.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Feature selection"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "USPTO provides several research datasets containing information about the patents and the application process. The enormous amount of data available required an advanced and complex data cleaning phase to extract meaningful features. Google Cloud Services, in particular the service BigQuery was extensively used to analyze terabytes of data to extract the features. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Overview of the datasets\n",
    "We used the following USPTO datasets: \n",
    "- [Patent Examination Research Dataset (PaTex)](https://www.uspto.gov/ip-policy/economic-research/research-datasets/patent-examination-research-dataset-public-pair)\n",
    "- [Patent Claims Research Dataset](https://www.uspto.gov/ip-policy/economic-research/research-datasets/patent-claims-research-dataset)\n",
    "\n",
    "We aim to build a dataset containing the following features\n",
    "\n",
    "| Feature | Description |\n",
    "|---------|-------------|\n",
    "|`application_number`| (will not be used for model training) |\n",
    "|`approved`| `1` if approved, `0` if rejected |\n",
    "|`examiner_art_unit` | Art unit of the examiner |\n",
    "|`uspc_class`| Class of USPC | \n",
    "|`uspc_subclass` | Subclass of USPC |\n",
    "|`inventors_count` | Number of inventors |\n",
    "|`inventors_avg_approval_rate` | Average approval rate of inventors | \n",
    "|`inventors_avg_patents` | Average number of patents per inventors\n",
    "|`inventors_country` | The country from which the majority of inventors come from\n",
    "|`attorney_count` | Number of attorneys |\n",
    "|`attorney_avg_approval_rate` | Average approval rate of attorneys | \n",
    "|`attorney_avg_patents` | Average number of patents per attorney |\n",
    "\n",
    "\n",
    "The dataset will contain: \n",
    "- Patents that were approved between 2010 and 2015 that were not rejected at the first attempt for non-novelty, for which we have information about the attorneys, the inventors and the patent claims\n",
    "- Applications that were rejected at the first attempt between 2010 and 2015, for which we have information about the attorneys, the inventors and the patent claims\n",
    "\n",
    "We will consider only applications in which the application type is `REGULAR` and the invetion type is `UTILITY`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Note\n",
    "The following SQL queries were executed on BigQuery on USPTO research datasets. Those datasets are already loaded on Google Cloud and have to be activated [here](https://console.cloud.google.com/marketplace/browse?q=google+patents+public+datasets&filter=solution-type:dataset)  \n",
    "The result of `SELECT` queries were saved in the tables listed in comments over the queries.  \n",
    "We runned several tests to verify that the results are accurate and corrent. The tests are not reported in this notebook."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Office actions filtering\n",
    "Let's start by filtering only approved applications between 2010 and 2015. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# application_data_approved_patents\n",
    "SELECT application_number, patent_number, filing_date, patent_issue_date, examiner_art_unit, uspc_class, uspc_subclass \n",
    "    FROM `patents-public-data.uspto_oce_pair.application_data` \n",
    "    WHERE patent_issue_date >= \"2010-01-01\" AND patent_issue_date <= \"2015-12-31\" AND application_type = \"REGULAR\" AND invention_subject_matter = \"UTL\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "With regards to rejected applications, we firstly need to consider the first office action for each application and select non-final rejections.     "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# first_office_actions\n",
    "SELECT * FROM `patents-public-data.uspto_oce_office_actions.office_actions` A, \n",
    "    (SELECT B.app_id, MIN(mail_dt) as min_date FROM `patents-public-data.uspto_oce_office_actions.office_actions` B\n",
    "    GROUP BY B.app_id) B\n",
    "    WHERE mail_dt >= \"2010-01-01\" AND mail_dt <= \"2015-12-31\" AND\n",
    "    A.app_id = B.app_id AND A.mail_dt = B.min_date \n",
    "\n",
    "\n",
    "# first_office_actions_2010_2015_non-novelty\n",
    "SELECT * FROM `ml-project2-333508.office-actions.first_office_actions` A\n",
    "    WHERE rejection_102 = 1  \n",
    "\n",
    "# application_data_rejected_non-novelty\n",
    "SELECT A.* FROM  `patents-public-data.uspto_oce_pair.application_data` A, \n",
    "    `ml-project2-333508.office-actions.first_office_actions_2010_2015_non-novelty` B\n",
    "    WHERE A.application_type = \"REGULAR\" AND invention_subject_matter = \"UTL\" AND\n",
    "    A.application_number = B.app_id    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Patent claims\n",
    "Let's now extract the patent claims for the selected applications. We will later remove the applications for which we can't find any patent claim. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# approved_patent_claims\n",
    "SELECT C.pat_no, C.claim_no, C.claim_txt \n",
    "    FROM `patents-public-data.uspto_oce_claims.patent_claims_fulltext` C \n",
    "    WHERE C.pat_no IN (SELECT patent_number FROM `ml-project2-333508.office_actions.application_data_approved_patents`) ORDER BY pat_no, claim_no\n",
    "\n",
    "# rejected_patent_claims\n",
    "SELECT C.pat_no, C.claim_no, C.claim_txt \n",
    "    FROM `patents-public-data.uspto_oce_claims.patent_claims_fulltext` C \n",
    "    WHERE C.pat_no IN (SELECT patent_number FROM `ml-project2-333508.office_actions.application_data_rejected_non-novelty`) ORDER BY pat_no, claim_no"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's now remove from `application_data` the applications that don't have any patent claim available.   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# application_data_approved_patents_with_claim\n",
    "SELECT A.* \n",
    "    FROM `ml-project2-333508.office_actions.application_data_approved_patents` A \n",
    "    WHERE A.patent_number IN (SELECT pat_no FROM `ml-project2-333508.patent_claims_fulltext.approved_patents_claims`) ORDER BY A.patent_number\n",
    "\n",
    "# application_data_rejected_non-novelty_with_claim\n",
    "SELECT A.* \n",
    "    FROM `ml-project2-333508.office_actions.application_data_rejected_non-novelty` A \n",
    "    WHERE A.patent_number IN (SELECT appl_id FROM `ml-project2-333508.patent_claims_fulltext.rejected_patents_claims`) ORDER BY A.patent_number\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There may exist some applications that were firstly rejected for non-novelty (with a non-final rejected) and that were later approved. We will remove those applications from the `approved` dataset and keep them in the `rejected` one. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# application_data_approved_filtered\n",
    "SELECT A.* \n",
    "    FROM `ml-project2-333508.office_actions.application_data_approved_patents_with_claim` A \n",
    "    WHERE A.application_number NOT IN \n",
    "        (SELECT B.application_number FROM `ml-project2-333508.office_actions.application_data_rejected_non-novelty_with_claim` B ) ORDER BY application_number"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We also have to compute again the table containing the patent claims for approved applications. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT A.*\n",
    "    FROM `ml-project2-333508.patent_claims_fulltext.rejected_patents_claims` A, \n",
    "    WHERE A.appl_id IN (SELECT B.application_number FROM `ml-project2-333508.office_actions.application_data_approved_filtered`)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's now tidy up the tables containg patent claims. Each claims starts with the number of the claim like the following example: \n",
    "\n",
    "| pat_no | claim_no | clam_txt | \n",
    "|----------|------|-----|\n",
    "|7640599| 1 | 1. A bib for illustrating an infant's last feeding time, said bib comprising \\[...\\] |\n",
    "|7640599| 2 | 2. A bib for illustrating an infant's last feeding time, according to claim 1 \\[...\\] |\n",
    "| 7640599 | \\[...\\] | \\[...\\] |"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# approved_patents_claims_filtered_cleaned\n",
    "SELECT pat_no, claim_no, SUBSTR(claim_txt, INSTR(claim_txt, \".\") + 2) AS claim_txt \n",
    "    FROM `ml-project2-333508.patent_claims_fulltext.approved_patents_claims_filtered` ORDER BY pat_no, CAST(claim_no AS INT)\n",
    "\n",
    "# rejected_patents_non-novelty_claims_cleaned\n",
    "SELECT appl_id, claim_no, SUBSTR(claim_txt, INSTR(claim_txt, \".\") + 2) AS claim_txt \n",
    "    FROM `ml-project2-333508.patent_claims_fulltext.rejected_patents_non-novelty_claims` ORDER BY appl_id, CAST(claim_no AS INT)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Inventors\n",
    "As stated in our paper, we believe that the history of an inventor will influence the outcome of future applications. \n",
    "For each patent application, we will compute: \n",
    "- The number of inventors\n",
    "- The average approval rate for the inventors\n",
    "- The average number of patent applications submitted by the inventors\n",
    "\n",
    "Those informations will be computed using applications between 2010 and 2015. \n",
    "\n",
    "Let's start by counting the number of approved and rejected patents for each inventor. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# inventors_rejected_count\n",
    "SELECT inventor_name_first, inventor_name_middle, inventor_name_last, inventor_country_code, COUNT(DISTINCT application_number)  as rejected_patents FROM `ml-project2-333508.inventors.inventors_rejected_patents` \n",
    "    GROUP BY inventor_name_first, inventor_name_middle, inventor_name_last, inventor_country_code\n",
    "\n",
    "# inventors_approved_count_filtered\n",
    "SELECT inventor_name_first, inventor_name_middle, inventor_name_last, inventor_country_code, COUNT(DISTINCT application_number)  as approved_patents FROM `ml-project2-333508.inventors.inventors_approved_patents_filtered` \n",
    "    GROUP BY inventor_name_first, inventor_name_middle, inventor_name_last, inventor_country_code\n",
    "\n",
    "# inventors_data_filtered_mixed (contains the information about the inventors that had both approved and rejected patents)\n",
    "SELECT A.inventor_name_first, A.inventor_name_middle, A.inventor_name_last, A.inventor_country_code, (A.approved_patents + R.rejected_patents) as total_patents, ROUND((A.approved_patents/(A.approved_patents + R.rejected_patents))*100, 2) as approval_rate\n",
    "    FROM `ml-project2-333508.inventors.inventors_approved_filtered_count` A, \n",
    "    `ml-project2-333508.inventors.inventors_rejected_count` R\n",
    "    WHERE A.inventor_name_first = R.inventor_name_first AND ((A.inventor_name_middle = R.inventor_name_middle) OR (A.inventor_name_middle IS NULL AND R.inventor_name_middle IS NULL)) AND A.inventor_name_last = R.inventor_name_last AND\n",
    "    ((A.inventor_country_code = R.inventor_country_code) OR (A.inventor_country_code IS NULL AND R.inventor_country_code IS NULL))\n",
    "\n",
    "# inventors_data_filtered_approved (only those who only have approved patents)\n",
    "SELECT A.inventor_name_first, A.inventor_name_middle, A.inventor_name_last, A.inventor_country_code, (A.approved_patents) as total_patents, 100.0 as approval_rate\n",
    "    FROM `ml-project2-333508.inventors.inventors_approved_patents_filtered_count` A\n",
    "    WHERE\n",
    "    NOT EXISTS\n",
    "    (SELECT R.inventor_name_first FROM `ml-project2-333508.inventors.inventors_rejected_count` R\n",
    "    WHERE R.inventor_name_first = A.inventor_name_first AND ((R.inventor_name_middle = A.inventor_name_middle) OR (R.inventor_name_middle IS NULL AND A.inventor_name_middle IS NULL)) AND \n",
    "    R.inventor_name_last = A.inventor_name_last AND ((R.inventor_country_code = A.inventor_country_code) OR (R.inventor_country_code IS NULL AND A.inventor_country_code IS NULL))\n",
    "    )\n",
    "\n",
    "# inventors_data_filtered_rejected (only those who only have rejected patents)\n",
    "SELECT A.inventor_name_first, A.inventor_name_middle, A.inventor_name_last, A.inventor_country_code, (A.rejected_patents) as total_patents, 0.0 as approval_rate\n",
    "    FROM `ml-project2-333508.inventors.inventors_rejected_count` A\n",
    "    WHERE\n",
    "    NOT EXISTS\n",
    "    (SELECT R.inventor_name_first FROM `ml-project2-333508.inventors.inventors_approved_patents_filtered_count` R\n",
    "    WHERE R.inventor_name_first = A.inventor_name_first AND ((R.inventor_name_middle = A.inventor_name_middle) OR (R.inventor_name_middle IS NULL AND A.inventor_name_middle IS NULL)) AND \n",
    "    R.inventor_name_last = A.inventor_name_last AND ((R.inventor_country_code = A.inventor_country_code) OR (R.inventor_country_code IS NULL AND A.inventor_country_code IS NULL))\n",
    "    )\n",
    "\n",
    "# inventors_data_filtered (we merge all the previous tables)\n",
    "(SELECT * FROM `ml-project2-333508.inventors.inventors_data_filtered_approved`)\n",
    "UNION ALL \n",
    "(SELECT * FROM `ml-project2-333508.inventors.inventors_data_filtered_mixed`)\n",
    "UNION ALL \n",
    "(SELECT * FROM `ml-project2-333508.inventors.inventors_data_filtered_rejected`)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we have a table (`inventors_data_filtered`) that contains the following columns: \n",
    "- `inventor_name_first`\n",
    "- `inventors_name_middle`\n",
    "- `inventor_name_last`\n",
    "- `inventor_country_code`\n",
    "- `total_patents` (patents approved + patents rejected for non-novelty at first attempt)\n",
    "- `approval_rate` (percentage of approved patents)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We now build the features for each application"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# features_inventors_approved\n",
    "SELECT A.application_number, AVG(I.approval_rate) as inventor_avg_approval_rate, AVG(I.total_patents) as inventor_avg_total_patents, COUNT(*) as inventors_number\n",
    "    FROM `ml-project2-333508.office_actions.application_data_approved_patents_filtered` A, \n",
    "    `ml-project2-333508.inventors.inventors_data_filtered` I,\n",
    "    `ml-project2-333508.inventors.inventors_approved_patents_filtered` I_A\n",
    "    WHERE A.application_number = I_A.application_number AND\n",
    "    I_A.inventor_name_first = I.inventor_name_first AND ((I_A.inventor_name_middle = I.inventor_name_middle) OR (I_A.inventor_name_middle IS NULL AND I.inventor_name_middle IS NULL)) AND I_A.inventor_name_last = I.inventor_name_last AND\n",
    "    ((I_A.inventor_country_code = I.inventor_country_code) OR (I_A.inventor_country_code IS NULL AND I.inventor_country_code IS NULL) )\n",
    "    GROUP BY A.application_number\n",
    "    ORDER BY A.application_number\n",
    "\n",
    "# features_inventors_rejected\n",
    "SELECT A.application_number, AVG(I.approval_rate) as inventor_avg_approval_rate, AVG(I.total_patents) as inventor_avg_total_patents, COUNT(*) as inventors_number\n",
    "    FROM `ml-project2-333508.office_actions.application_data_rejected_non-novelty_with_claim` A, \n",
    "    `ml-project2-333508.inventors.inventors_data_filtered` I,\n",
    "    `ml-project2-333508.inventors.inventors_rejected_patents` I_A\n",
    "    WHERE A.application_number = I_A.application_number AND\n",
    "    I_A.inventor_name_first = I.inventor_name_first AND ((I_A.inventor_name_middle = I.inventor_name_middle) OR (I_A.inventor_name_middle IS NULL AND I.inventor_name_middle IS NULL)) AND I_A.inventor_name_last = I.inventor_name_last AND\n",
    "    ((I_A.inventor_country_code = I.inventor_country_code) OR (I_A.inventor_country_code IS NULL AND I.inventor_country_code IS NULL) )\n",
    "    GROUP BY A.application_number\n",
    "    ORDER BY A.application_number"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Attorneys\n",
    "We will do the same computations that we did with inventors. \n",
    "For each patent application, we will compute: \n",
    "- The number of attorneys\n",
    "- The average approval rate for the attorneys\n",
    "- The average number of patent applications submitted by the attorneys\n",
    "\n",
    "Those informations will be computed using applications between 2010 and 2015. \n",
    "\n",
    "Let's start by counting the number of approved and rejected patents for each attorney. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# attorneys_approved_patents\n",
    "SELECT application_number, atty_registration_number FROM `ml-project2-333508.attorneys.attorney_agent`A WHERE \n",
    "    EXISTS (SELECT B.application_number FROM `ml-project2-333508.office_actions.application_data_approved_patents_filtered` B WHERE B.application_number = A.application_number)\n",
    "\n",
    "# attorneys_rejected_patents\n",
    "SELECT application_number, atty_registration_number FROM `ml-project2-333508.attorneys.attorney_agent`A WHERE \n",
    "    EXISTS (SELECT B.application_number FROM `ml-project2-333508.office_actions.application_data_rejected_non-novelty_with_claim` B WHERE B.application_number = A.application_number)\n",
    "\n",
    "# attorneys_approved_count\n",
    "SELECT A.atty_registration_number, COUNT(DISTINCT A.application_number) as approved_patents FROM `ml-project2-333508.attorneys.attorneys_approved_patents` A \n",
    "    GROUP BY A.atty_registration_number \n",
    "    ORDER BY A.atty_registration_number\n",
    "\n",
    "# attorneys_rejected_patents_count\n",
    "SELECT A.atty_registration_number, COUNT(DISTINCT A.application_number) as rejected_patents FROM `ml-project2-333508.attorneys.attorneys_rejected_patents` A \n",
    "    GROUP BY A.atty_registration_number \n",
    "    ORDER BY A.atty_registration_number\n",
    "\n",
    "# attorneys_data_mixed\n",
    "SELECT A.atty_registration_number, (A.approved_patents + R.rejected_patents) as total_patents, ROUND((A.approved_patents/(A.approved_patents + R.rejected_patents))*100, 2) as approval_rate\n",
    "    FROM `ml-project2-333508.attorneys.attorneys_approved_patents_count` A, \n",
    "    `ml-project2-333508.attorneys.attorneys_rejected_patents_count` R\n",
    "    WHERE A.atty_registration_number = R.atty_registration_number\n",
    "\n",
    "# attorneys_data_approved\n",
    "SELECT A.atty_registration_number, (A.approved_patents) as total_patents, 100.0 as approval_rate\n",
    "    FROM `ml-project2-333508.attorneys.attorneys_approved_patents_count` A\n",
    "    WHERE\n",
    "    NOT EXISTS\n",
    "    (SELECT R.atty_registration_number FROM `ml-project2-333508.attorneys.attorneys_rejected_patents_count` R\n",
    "    WHERE R.atty_registration_number = A.atty_registration_number)\n",
    "\n",
    "# attorneys_data_rejected\n",
    "SELECT A.atty_registration_number, (A.rejected_patents) as total_patents, 0.0 as approval_rate\n",
    "    FROM `ml-project2-333508.attorneys.attorneys_rejected_patents_count` A\n",
    "    WHERE\n",
    "    NOT EXISTS\n",
    "    (SELECT R.atty_registration_number FROM `ml-project2-333508.attorneys.attorneys_approved_patents_count` R\n",
    "    WHERE R.atty_registration_number = A.atty_registration_number)\n",
    "\n",
    "# attorneys_data\n",
    "(SELECT * FROM `ml-project2-333508.attorneys.attorneys_data_mixed`)\n",
    "UNION ALL \n",
    "(SELECT * FROM `ml-project2-333508.attorneys.attorneys_data_approved`)\n",
    "UNION ALL \n",
    "(SELECT * FROM `ml-project2-333508.attorneys.attorneys_data_rejected`)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we have a table (`attorneys_data`) that contains the following columns: \n",
    "- `atty_registration_number`\n",
    "- `total_patents` (patents approved + patents rejected for non-novelty at first attempt)\n",
    "- `approval_rate` (percentage of approved patents)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We now build the features for each approved and rejected application"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# features_attorneys_approved\n",
    "SELECT A.application_number, AVG(I.approval_rate) as attorney_avg_approval_rate, AVG(I.total_patents) as attorney_avg_total_patents, COUNT(*) as attorneys_number\n",
    "    FROM `ml-project2-333508.office_actions.application_data_approved_patents_filtered` A, \n",
    "    `ml-project2-333508.inventors.attorneys_data` I,\n",
    "    `ml-project2-333508.inventors.attorneys_approved_patents` I_A\n",
    "    WHERE A.application_number = I_A.application_number AND I_A.atty_registration_number = I.atty_registration_number\n",
    "    GROUP BY A.application_number\n",
    "    ORDER BY A.application_number\n",
    "\n",
    "# features_attorneys_rejected\n",
    "SELECT A.application_number, AVG(I.approval_rate) as attorney_avg_approval_rate, AVG(I.total_patents) as attorney_avg_total_patents, COUNT(*) as attorneys_number\n",
    "    FROM `ml-project2-333508.office_actions.application_data_rejected_non-novelty_with_claim` A, \n",
    "    `ml-project2-333508.inventors.attorneys_data` I,\n",
    "    `ml-project2-333508.inventors.attorneys_rejected_patents` I_A\n",
    "    WHERE A.application_number = I_A.application_number AND I_A.atty_registration_number = I.atty_registration_number\n",
    "    GROUP BY A.application_number\n",
    "    ORDER BY A.application_number"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Combine the features\n",
    "We have 4 different tables: `features_inventors_approved`, `features_inventors_rejected`, `features_attorneys_approved`, `features_attorneys_rejected`. Let's combine them. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# features_approved\n",
    "SELECT A.application_number, \"1\" as approved, A.examiner_art_unit, A.uspc_class, A.uspc_subclass, I.inventors_number, ROUND(I.inventor_avg_approval_rate, 2) as inventors_avg_approval_rate, ROUND(I.inventor_avg_total_patents, 2) as inventors_avg_toal_patents, T.attorney_number as attorneys_number, ROUND(T.attorney_avg_total_patents, 2) as attorneys_avg_total_patents, ROUND(T.attorney_avg_approval_rate, 2) as attorneys_avg_approval_rate\n",
    "    FROM `ml-project2-333508.office_actions.application_data_approved_patents_filtered` A,\n",
    "    `ml-project2-333508.features.features_inventors_approved` I,\n",
    "    `ml-project2-333508.features.features_attorneys_approved` T\n",
    "    WHERE A.application_number = I.application_number AND A.application_number = T.application_number\n",
    "    ORDER BY A.application_number\n",
    "\n",
    "# features_rejected\n",
    "SELECT A.application_number, \"0\" as approved, A.examiner_art_unit, A.uspc_class, A.uspc_subclass, I.inventors_number, ROUND(I.inventor_avg_approval_rate, 2) as inventors_avg_approval_rate, ROUND(I.inventor_avg_total_patents, 2) as inventors_avg_toal_patents, T.attorney_number as attorneys_number, ROUND(T.attorney_avg_total_patents, 2) as attorneys_avg_total_patents, ROUND(T.attorney_avg_approval_rate, 2) as attorneys_avg_approval_rate\n",
    "    FROM `ml-project2-333508.office_actions.application_data_rejected_non-novelty_with_claim` A,\n",
    "    `ml-project2-333508.features.features_inventors_rejected` I,\n",
    "    `ml-project2-333508.features.features_attorneys_rejected` T\n",
    "    WHERE A.application_number = I.application_number AND A.application_number = T.application_number\n",
    "    ORDER BY A.application_number"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Some applications don't contain information about the inventors or attorneys. We now filter patent claims again in order to remove those applications. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# features_approved_claims (we substitute pat_no with application_number and filter again)\n",
    "SELECT F.application_number, P.claim_no AS claim_number, P.claim_txt AS claim_text FROM `ml-project2-333508.features.features_approved` F, `ml-project2-333508.office_actions.application_data_approved_patents_filtered` A, `ml-project2-333508.patent_claims_fulltext.approved_patents_claims_filtered_cleaned` P\n",
    "    WHERE F.application_number = A.application_number AND P.pat_no = A.patent_number\n",
    "    ORDER BY F.application_number, CAST(P.claim_no AS INT)\n",
    "\n",
    "# features_rejected_claims \n",
    "SELECT F.application_number, P.claim_no AS claim_number, P.claim_txt AS claim_text FROM `ml-project2-333508.features.features_rejected` F,  `ml-project2-333508.patent_claims_fulltext.rejected_patents_non-novelty_claims_cleaned` P\n",
    "    WHERE P.appl_id = F.application_number\n",
    "    ORDER BY F.application_number, CAST(P.claim_no AS INT)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We finally combine all the features"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# features \n",
    "SELECT * FROM\n",
    "(SELECT * FROM `ml-project2-333508.features.features_approved`)\n",
    "UNION ALL \n",
    "(SELECT * FROM `ml-project2-333508.features.features_rejected`)\n",
    "ORDER BY application_number\n",
    "\n",
    "\n",
    "# features_claims\n",
    "SELECT * FROM\n",
    "(SELECT * FROM `ml-project2-333508.features.features_approved_claims`)\n",
    "UNION ALL \n",
    "(SELECT * FROM `ml-project2-333508.features.features_rejected_claims`)\n",
    "ORDER BY application_number"
   ]
  }
 ],
 "metadata": {
  "language_info": {
   "name": "python"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}