Difference between revisions of "Patent Assignment Data Restructure"

From edegan.com
Jump to navigation Jump to search
Line 35: Line 35:
 
The final deliverable for the semester is a table with the following structure:  
 
The final deliverable for the semester is a table with the following structure:  
  
  Reel No | Frame No | Invention Title | Filing Date | Patent No | Application No | Publication No | Match By   
+
  Reel No | Frame No | Invention Title | Filing Date | Patent No | Application No | Publication No | Match By
 +
 
 +
The schema of the table will be:
 +
 
 +
  Column          |          Type        | Modifiers
 +
  -----------------+-----------------------+-----------
 +
Reel No          |        integer        |
 +
Frame No        |        integer        |
 +
Invention Title  | character varying(500)|
 +
Filing Date      |          date        |
 +
Patent No        |        integer        |
 +
Application No  |        integer        |
 +
Publication No  |        integer        |
 +
Match By        |        integer        |
  
 
==Data Cleanup Progress==
 
==Data Cleanup Progress==

Revision as of 16:45, 20 March 2017


McNair Project
Patent Assignment Data Restructure
Project logo 02.png
Project Information
Project Title Patent Data Restructure
Owner Marcela Interiano, Sonia Zhang
Start Date 201701
Deadline 201705
Keywords Patent
Primary Billing
Notes
Has project status Active
Subsumes: Patent Data (Wiki Page)
Copyright © 2016 edegan.com. All Rights Reserved.


In order to restructure the current patent dataset, the data requires rigorous cleaning. The primary areas for improvement are:

1. Clean ptoassignment table to unique keys.
2. Clean ptoproperties to remove nonutility patents. The patent numbers currently include:
  • 7 digit patent numbers
  • application numbers
  • unknown numbers that cannot be matched to patent numbers in the patent table
20090108066
20100007288
20090108066
20100110022
  • Design and Reissue patents ('%D%' or '%RE%')
  • alphanumeric character strings
3. Restructure address information in ptoassignee table to extract meaningful information
4. Verify that cleaned patent documentids correspond to patent numbers or application numbers in the patent table
5. Restructure address information in ptoassignment table
6. Transform structure of the dataset

Semester Plan

The final deliverable for the semester is a table with the following structure:

Reel No | Frame No | Invention Title | Filing Date | Patent No | Application No | Publication No | Match By

The schema of the table will be:

 Column          |          Type         | Modifiers
-----------------+-----------------------+-----------
Reel No          |        integer        |
Frame No         |        integer        |
Invention Title  | character varying(500)|
Filing Date      |          date         |
Patent No        |        integer        |
Application No   |        integer        |
Publication No   |        integer        |
Match By         |        integer        |

Data Cleanup Progress

Patent Number Cleanup

The goal is to only have assignment records on utility patents. The patents in ptoproperty include alphanumerics which represent reissue and design patents as well as mistakes in the data input. Additionally, the documentids include application numbers or ids and publication numbers. The ptoproperty table stores the patent ids as character strings.

First the duplicates were dropped from the ptoproperty table creating ptoproperty_cleaned.

SELECT COUNT(*) FROM (SELECT DISTINCT * FROM ptoproperty) As T; 
--27266638
 
SELECT COUNT(*) FROM ptoproperty_cleaned;
--27266638

Next, the Reissue and Design patents were removed.

SELECT COUNT(*) FROM ptoproperty WHERE documentid LIKE 'RE%';
--38512
SELECT COUNT(*) FROM ptoproperty WHERE documentid LIKE 'D%';
--1128247

Restructure Address Information

The dbase is patent.

The table is ptoassigneend.

SQL code and other things are in:

E:/McNair/Projects/PatentAddress

IO files are on the dbase server in:

Z:/PatentAddress
1. Introduction
  • Five features (addrline1, addrline2, city, country, postcode) in the table contain address information.
  • Features addrline1, addrline2 and city are not cleaned. They have city, country and postcode information.
  • The object of this project is to extract city, country and postcode information from the three features above.
  • By now, we only focus on cleaning American patents.
2. Postcode and State (U.S.)
U.S. post code follows the pattern [five digits - four digits]. In this way, U.S. patents can be extracted by searching for post code with regular expression.
The state and post code are always together, separated by a space. So we can also extract state information with regular expression too.
SQL code are in:
E:/McNair/Projects/PatentAddress/RxPostcode.sql
The extracted records are stored in table ptoassigneend_missus.
3. City (U.S.)


4. Issues
  • The ultimate post code and zip regex for other countries besides U.S. can be found here:
http://stackoverflow.com/questions/578406/what-is-the-ultimate-postal-code-and-zip-regex
  • The city feature needs to be standardized. For example, 'GRAND CAYMAN, CAYMAN ISLAND' and 'GRAND CAYMAN' indicate the same city.
  • Some state and country features don't match. For example, 'Beijing' - 'UNITED STATES, 10022'.