||Has keywords=Patent,Data|Has project status=ActiveSubsume|Does subsume=Patent Data (Wiki Page), Patent Data Cleanup - June 2016, Patent Data Extraction Scripts (Tool), USPTO Bulk Data Processing,
}}
In order to restructure the current patent dataset, the data requires rigorous cleaning. The primary areas for improvement are:
==Data Cleanup Progress==
The tables that currently comprise the assignment data are as follows.
This section explains the series of steps that were taken to clean and to take note of problems in the data. Additionally, this section includes the codes for new tables that combine patent properties from different tables in the original assignment data.
===Table Cleanup===
The five original assignment tables contain duplicates that were deleted. Five new tables were made and used as templates for building new tables.
Ptoassignmentnd Table:
SELECT COUNT(*) FROM ptoassignment;
--8676322
DROP ptoassignmentnd;
CREATE TABLE ptoassignmentnd AS
SELECT reelno, frameno, max(last_update_date) as last_update_date, purge_indicator, recorded_date, correspondent_name,
SELECT DISTINCT reel_no, frame_no, action_key_code, uspto_transaction_date, uspto_date_produced, version FROM ptopatentfile;
--7159725
===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 ptoproperty WHERE documentid LIKE 'D%';
--1128247
Finally, all letters were removed from the data, resulting in the final version of ptoproperty_cleaned.
The ptoproperty_cleaned table contains application numbers, publication numbers, and patent numbers. The patents may also have two distinct publication numbers based on the year in which it was published. Based on length of documentid, the three types of id numbers were separated into three separate tables.
DROP TABLE ptoproperty_patent;
CREATE TABLE ptoproperty_patent AS
SELECT * FROM ptoproperty_cleaned WHERE length(documentid) = 7;
--8696149
ALTER TABLE ptoproperty_patent RENAME COLUMN documentid TO patentno;
DROP TABLE ptoproperty_app;
CREATE TABLE ptoproperty_app AS
SELECT * FROM ptopropertynd WHERE length(documentid) = 8;
--11577028
ALTER TABLE ptoproperty_app RENAME COLUMN documentid TO appno;
DROP TABLE ptoproperty_pub;
CREATE TABLE ptoproperty_pub AS
SELECT * FROM ptopropertynd WHERE length(documentid) = 11;
--6217864
ALTER TABLE ptoproperty_pub RENAME COLUMN documentid TO pubno;
All three tables contain the following information and differ only on the type of patent id.
The purpose of the ptotracking tables is to track the ownership of patents based on the update date and filing dates of the assignment. These tables can be used to add missing information or track further refined tables such as ptoproperty_patent.
Ptotracking takes the reelno, frameno, and documentid key from the ptoproperty_cleaned table and joins the update dates and recorded dates corresponding to the transactions.
WHERE (M1.reelno = M2.reelno) AND (M1.frameno = M2.frameno);
--8699074
In making the ptotracking table, it is important to note that the classification of documentids as B1 and B2 causes duplicates in the entries. B1 and B2 classifications mean that the patent was granted with and without a published application.
Ptotracking2 adds the assignee to the transaction, allowing the user to track ownership of the entity and of the patent.
WHERE (M1.reelno = M2.reelno) AND (M1.frameno = M2.frameno);
--9613927
The document ids in the PTO assignment data had not yet been verified as matching to the main patent table in our database (psql patent). The document ids in the PTO assignment data are stored as character strings whereas the patents in the patent table are stored as integers. Unlike the ptoprpoperty_cleaned table, all patent numbers in the patent table are unique.
The following two tables were made in order to verify that the documentids in the ptoproperty_cleaned table match to the patent table.
DROP TABLE edcheck;
CREATE TABLE edcheck AS
SELECT CAST (documentid AS INT) FROM ptotracking2;
SELECT COUNT(DISTINCT documentid) FROM edcheck;
--2343765
DROP TABLE edcheck2;
CREATE TABLE edcheck2 AS
SELECT M1.documentid, M2.patent
FROM edcheck M1, patent M2
WHERE (M1.documentid = M2.patent);
--2238305
DROP TABLE edcheck;
CREATE TABLE edcheck AS
SELECT DISTINCT documentid FROM ptotracking2;
--2343765
DROP TABLE edcheck2;
CREATE TABLE edcheck2 AS
SELECT CAST(documentid AS INT)FROM edcheck;
--2343765
DROP TABLE edcheck3;
CREATE TABLE edcheck3 AS
SELECT M1.documentid, M2.patent FROM edcheck2 M1, patent M2 WHERE M1.documentid = M2.patent;
--2238305
Based on the iterations of these tables, we could conclude that our original patent data forms the majority of the patents undergoing reassignments or transactions.
===US ONLY Patent Assignee Table===
Note: Table made for Julia by [[Marcela Interiano]]
Table made in the patent database using the USPTO assignment data.
The first step was to include the last_update_date in with the data from the ptoproperty table. The ptoproperty table contains only the filing date, which is not useful as we are looking for the current patent holders. The table ptoproperty_patent was used for the patent numbers as this table was cleaned to include only patent numbers, no application or publication numbers.
FROM ptoproperty_patent_minupdate M1, ptoassigneend_us_cleaned M2
WHERE (M1.reelno = M2.reelno) AND (M1.frameno = M2.frameno);
The table below was made to join through using Sonia's zip codes for the ptoassignee data to get patent numbers from reelno and frameno.
DROP TABLE ptoassignee_us_distinct;
CREATE TABLE ptoassignee_us_distinct AS
SELECT DISTINCT reelno, frameno, patentno
FROM ptoassignee_us_patent
GROUP BY reelno, frameno, patentno;
--5391413
The total number of distinct patent numbers in the ptoassignee data for only US assignees is 2345763.
SELECT COUNT(*) FROM (SELECT DISTINCT patentno FROM ptoassignee_us_patent) AS P;
--2345763
===Current Assignee using Recorded Date===
Each assignment has three dates: filingdate, recorded_date, last_update_date. The filingdate corresponds to the filing of the assignment with the USPTO. The recorded_date is the date the transaction was recorded. The last_update_date is the date the USPTO verifies that the assignment still holds. In the ptoassignee_us_patent table, the last_update_date is used to find the current assignee.
Prior to Sonia's work with the ptoassignee table address data, the table ptoassignee_current was made using the most recent recorded_date. This method though is flawed given that additional transactions could have current previously that are still in effect as patents can have multiple assignees. These codes can be used for constructing similar tables using the address data Sonia has cleaned in the following sections of this project.
To begin with, the ptoproperty_patent table was cleaned to drop all duplicates. Then the table was matched with the assignee table.
WHERE (M1.reelno = M2.reelno) AND (M1.frameno = M2.frameno);
--9634942
Once all the location and address fields from the ptoassignee table have been added to the ptoproperty_patent fields, the max recorded_date was identified from the ptoassignee_patent table and from ptoassigneev2 for comparison.
DROP TABLE datecheck;
CREATE TABLE datecheck AS
SELECT documentid, max(recorded_date) as recorded_date FROM ptoassignee_patent GROUP BY documentid;
--2343765
DROP TABLE datecheck;
CREATE TABLE datecheck AS
SELECT documentid, max(recorded_date) as recorded_date FROM ptoassigneev2 GROUP BY documentid;
FROM ptotracking2 M1, ptoassigneend M2 WHERE (M1.reelno = M2.reelno) AND (M1.frameno = M2.frameno);
--16581236
DROP TABLE ptoassignee_current;
CREATE TABLE ptoassignee_current AS
SELECT M1.reelno, M1.frameno, M2.documentid, M2.recorded_date FROM ptoassignee_patent M1, datecheck M2
WHERE (M1.documentid = M2.documentid)
AND (M1.recorded_date = M2.recorded_date);
--6729698
A final version of the ptoassignee_current table was made using ptoassigneev2 given the larger pool of documentids included in the table by matching using documentid and recorded dates from datecheck.
As mentionedin Section 3, the ptoassigneend_us_extracted is cleanedclean. Copy all the records in ptoassigneend_us_extracted to ptoassigneend_us_identify0.
Store remaining records in ptoassigneend_us_temp.
==== Output: ptoassigneend_us_identify1 ====
The following section works on the remaining records which are stored in ptoassigneend_us_temp.
First, filter Filter out records with city that is a city, zip that is a zip, state that is a state.
Note: The consistency between city and state or city and postcode is not checked in this section.
* ptoassigneend_us_citylist
Select Copy clean city records in ptoassigneend_us_extracted and store them in to ptoassigneend_us_citylist (775).
Since the city list is not long, I briefly cleaned the list by hand, and updated the ptoassigneend_us_citylist (730).
* zip that is a zip
Match the pattern 55d-4 4d or 5 digits.
*state that is a state
*city that is a city
One option method to identify clean city is to find city records that match ptoassigneend_us_citylist.
SQL Code:
-- SELECT 2603422
* The table ptoassigneend_us_identify1 stores records that meet all the requirements above: zip with 5-4 or 5 digits, state not null or and not spaces, and city in ptoasigneend_us_citylist.
SQL Code:
==== Output: ptoassigneend_us_identify2 ====
Part of 'city' contains commaat the end. Remove comma, and then match 'city' with ptoassigneend_us_citylist.
SELECT *, replace(city, ',', '') clean_city
# SELECT 14508
Store remaining data (excluding data in ptoassigneend_us_identify0, ptoassigneend_us_identify1 & ptoassigneend_us_identify2) in ptoassigneend_temp3ptoassigneend_us_temp3.
SQL code is in E:\McNair\Projects\PatentAddress\Cleaning_Step2RestructureAddressInfo(Second Stage).sql
====Clean Postcode====
Identifying five-digit postcode is risky because of the existence of P.O. BOX #, SUITE #, etc.
One option is to identify state and postcode together with the following SQL codefunction: (take 'addrline1' as an example)
SQL function:
CREATE OR REPLACE FUNCTION ExtractPostcode2(adr text) RETURNS text AS $$
SELECT CASE WHEN (adr ~* '([,]|[.])\s\w{2,}\s{0,}\w{0,}\s{1,}\d{5}' OR
767 FIFTH AVE., NEW YORK, NY 10153 | 10153
Even excluding the P.O. BOX # and SUITE #, noise Noise still exists.
After extracting postcode, next, the following function is used to get clean postcode.
The priority is 'postcode' if it is '\d{5}', postcode_addr1, postcode_addr2, postcode_city
CREATE OR REPLACE FUNCTION PostcodeClean2 (text,text,text,text) RETURNS text AS $$
$pri1=$_[0];
$pri2=$_[1];
if ($pri3) {return $pri3;}
return undef;
$$ LANGUAGE plperl;
The details and SQL function are in E:\McNair\Projects\PatentAddress\Cleang_Step2RestructureAddressInfo(Second Stage).sql
The output is table ptoassigneend_us_postex which include a new feature 'postcode_extracted'.
====Clean 'city'====
'city' can be is cleaned using the following patterns.
*Pattern 1: 'city' is like ~ 'city name, state ID'
*Pattern 2: 'city' is like ~ 'city name, state postcode (5 digits)'
*Pattern 3: 'city' is like ~ 'city name,'
The SQL function is:
$$ LANGUAGE SQL;
The details and SQL function are in E:\McNair\Projects\PatentAddress\Cleang_Step2RestructureAddressInfo(Second Stage).sql
The output is table ptoassigneend_us_postex2 which include a new feature 'city_extracted' and 'postcode_extracted'.
===Identify Clean Data (Round Two)===
A new list of clean city is extracted in Section 4.3.2. This list, combined with 'ptoassigneend_us_citylist', creates a new city list 'ptoassigneend_us_citylist2' which can be used to identify clean data.
Since the city list is not long, I briefly cleaned the list it by hand, and stored it in ptoassigneend_us_citylist2.
* Actually, we can buy find a full list of U.S. cities online: https://www.uscitieslist.org/.
====Output: ptoassigneend_us_identify3====
Similar to Section 4.2, identify clean data that meets all the requirements: postcode_extracted with 5-4 or 5 digits, state not null or and not spaces, and city_extracted in ptoasigneend_us_citylist2.
SQL Code:
====Output: ptoassigneend_us_identify4====
Some of the city records contain dots. Remove dots, and then match 'city' with ptoassigneend_us_citylist2.
SQL Code:
CREATE TABLE ptoassigneend_us_identify4 AS
The remaining records are stored in ptoassigneend_us_temp5.
One problem for the REMAINING records is that the postcode is missing. ====Output: ptoassigneend_us_identify_subtotal====
If we relax Union ptoassigneend_us_identify(0-4) to generate ptoassigneend_us_identify_subtotal with clean city, state and postcode. This table contains 89.5% of all the requirements for postcode, we'll get clean data records in ptoassigneend_allus. 10.5% left in ptoassigneend_us_temp5. Table "public.ptoassigneend_us_identify_subtotal" Column | Type | Modifiers -----------------|------------------------|----------- reelno | integer | frameno | integer | name | character varying(500) | addrline1 | character varying(500) | addrline2 | character varying(500) | city_cleaned | text | state_cleaned | text | country | character varying(city 500) | postcode_cleaned | text | ====Output: ptoassigneend_us_candid1==== One problem of records in ptoassigneend_us_temp5 is that the postcode is missing. ptoassigneend_us_candid1 is a subset of ptoassigneend_us_temp5. It contains clean cityand state info, state that but postcode is a state) stored in ptoassigneend_us_identify4missing.
SQL code:
CREATE TABLE ptoassigneend_us_identify5 ptoassigneend_us_candid1 AS
SELECT *
FROM ptoassigneend_us_temp5
Remaining records are in table ptoassigneend_us_temp6 (SELECT 239837).
===Summary=Output: ptoassigneend_us_candid2==== Table Name | Records # ---------------------------|------------- ptoassigneend_allus | 3572605 ---------------------------|------------- ptoassigneend_us_identify0 | 5343 ptoassigneend_us_temp | 3567261 ptoassigneend_us_identify1 | 2511356 ptoassigneend_us_temp2 | 1055874 ptoassigneend_us_identify2 | 14508 ptoassigneend_us_temp3 | 1041366 ptoassigneend_us_identify3 | 664524 ptoassigneend_us_temp4 | 376835 ptoassigneend_us_identify4 | 38ptoassigneend_us_candid2 is also a subset of ptoassigneend_us_temp5 | 376797 ptoassigneend_us_identify5 | 136958 ptoassigneend_us_temp6 | 239837Union ptoassigneend_us_identify(0-4) to get ptoassigneend_us_identify_subtotal (3195769). 10.5% left in ptoassigneend_us_temp5It contains clean postcode info, but city and state are not identified.
ptoassigneend_us_identify5 doesn't contain I randomly checked the city_extracted in ptoassigneend_us_candid2, and it is quite clean passcode info. Some city records are misspelt, but has such as 'Oklahama City'. We may identify clean city and state info. 6.7% data left in ptoassigneend_us_temp6based on the length of records.
Note: About 60 records are missing. For example, the # of records in ptoassigneend_us_temp + # of records in ptoassigneend_us_identify0 != # ptoassigneend_allus.
The # of records in ptoassigneend_us_temp + # of records in ptoassigneend_us_identify0 != ptoassigneend_allus===To do====* Remove city, state, zip, country from addrline1 & addrline2 to get clean addrlines.* Maybe concatenate addrline1 and addrline to make addrline