Changes

23,095 bytes added , 13:41, 21 September 2020

no edit summary

{{Project|Has project output=Data|Has sponsor=McNair ~~Projects~~Center

|Has title=Patent Data Restructure

|Has owner=Marcela Interiano, Sonia Zhang,

|Has start date=201701

|Has deadline=201705

||Has keywords=Patent,Data|Has project status=~~Active~~Subsume|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.

Table "public.ptoassignment"

Column | Type | Modifiers

-------------------------+------------------------+-----------

reelno | integer |

frameno | integer |

last_update_date | date |

purge_indicator | character varying(2) |

recorded_date | date |

correspondent_name | character varying(500) |

correspondent_address_1 | character varying(500) |

correspondent_address_2 | character varying(500) |

correspondent_address_3 | character varying(500) |

correspondent_address_4 | character varying(500) |

conveyance_text | character varying(500) |

Table "public.ptoassignee"

Column | Type | Modifiers

-----------+------------------------+-----------

reelno | integer |

frameno | integer |

name | character varying(500) |

addrline1 | character varying(500) |

addrline2 | character varying(500) |

city | character varying(500) |

state | character varying(500) |

country | character varying(500) |

postcode | character varying(80) |

Table "public.ptoassignor"

Column | Type | Modifiers

----------------+------------------------+-----------

reel_no | integer |

frame_no | integer |

assignor_name | character varying(500) |

execution_date | date |

Table "public.ptoproperty"

Column | Type | Modifiers

-----------------+------------------------+-----------

reelno | integer |

frameno | integer |

documentid | character varying(20) |

country | character varying(500) |

kind | character varying(3) |

filingdate | date |

invention_title | character varying(500) |

Table "public.ptopatentfile"

Column | Type | Modifiers

------------------------+-----------------------+-----------

reel_no | integer |

frame_no | integer |

action_key_code | character varying(10) |

uspto_transaction_date | date |

uspto_date_produced | date |

version | numeric |

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,

correspondent_address_1, correspondent_address_2, correspondent_address_3, correspondent_address_4, conveyance_text

FROM ptoassignment GROUP BY reelno, frameno, purge_indicator, recorded_date, correspondent_name, correspondent_address_1,

correspondent_address_2, correspondent_address_3, correspondent_address_4, conveyance_text;

--6988575

Ptoassigneend Table:

SELECT COUNT(*) FROM ptoassignee;

--8983280

DROP ptoassigneend;

CREATE TABLE ptoassigneend AS

SELECT DISTINCT reelno, frameno, name, addrline1, addrline2, city, state, country, postcode FROM ptoassignee;

--7234001

Ptoassignornd Table:

SELECT COUNT(*) FROM ptoassignor;

--20062463

DROP ptoassignornd;

CREATE TABLE ptoassinornd AS

SELECT DISTINCT reel_no, frame_no, assignor_name, execution_date FROM ptoassignor;

--16126903

Ptoproperty_cleaned Table:

SELECT COUNT(*) FROM ptoproperty;

--65214396

DROP TABLE ptoproperty_cleaned;

CREATE TABLE ptoproperty_cleaned AS

SELECT DISTINCT reelno, frameno, documentid, country, kind, filingdate, invention_title

FROM ptoproperty;

--8696149

Ptopatentfilend Table:

SELECT COUNT(*) FROM ptopatentfile;

--8676317

DROP ptopatentfilend;

CREATE TABLE ptopatentfilend AS

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.

Table "public.ptoproperty_patent"

Column | Type | Modifiers

-----------------+------------------------+-----------

reelno | integer |

frameno | integer |

documentid | character varying(20) |

country | character varying(500) |

kind | character varying(3) |

filingdate | date |

invention_title | character varying(500) |

===Ptotracking Tables===

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.

DROP TABLE ptotracking;

CREATE TABLE ptotracking AS

SELECT M1.reelno, M1.frameno, M1.documentid, M2.last_update_date, M2.recorded_date

FROM ptoproperty_cleaned M1, ptoassignmentnd M2

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.

DROP TABLE ptotracking2;

CREATE TABLE ptotracking2 AS

SELECT M1.reelno, M1.frameno, M1.documentid, M2.name, M1.last_update_date, M1.recorded_date

FROM ptotracking M1, ptoassigneend M2

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.

DROP TABLE ptoproperty_patent_update;

CREATE TABLE ptoproperty_patent_update AS

SELECT M1.reelno, M1.frameno, M1.patentno, M2.last_update_date

FROM ptoproperty_patent M1, ptotracking2 M2

WHERE (M1.reelno = M2.reelno) AND (M1.frameno = M2.frameno) AND (M1.patentno = M2.documentid);

Next, the minimum update date was taken, dropping any repetitions or later dates for the same patent assignee.

DROP TABLE ptoproperty_patent_minupdate;

CREATE TABLE ptoproperty_patent_minupdate AS

SELECT reelno, frameno, patentno, min(last_update_date) FROM ptoproperty_patent_update GROUP BY reelno, frameno, patentno, last_update_date;

The US only assignee table was used to construct the final table.

DROP TABLE ptoassignee_us_patent;

CREATE TABLE ptoassignee_us_patent AS

SELECT M1.reelno, M1.frameno, M2.name, M1.patentno, M1.last_update_date

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.

DROP TABLE ptoassigneev1;

CREATE TABLE ptoassigneev1 AS

SELECT M1.reelno, M1.frameno, M1.documentid, M1.country, M1.filingdate, M2.last_update_date,

M2.recorded_date

FROM ptoproperty_patent2 M1, ptoassignmentnd M2

WHERE (M1.reelno = M2.reelno) AND (M1.frameno = M2.frameno);

DROP TABLE ptoassigneev2;

CREATE TABLE ptoassigneev2 AS

SELECT M1.reelno, M1.frameno, M1.documentid, M2.name, M1.country, M1.last_update_date, M1.recorded_date,

M2.addrline1, M2.addrline2, M2.city, M2.state, M2.postcode

FROM ptoassigneev1 M1, ptoassigneend M2

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;

--4374885

DROP TABLE ptoassignee_patent;

CREATE TABLE ptoassignee_patent AS

SELECT M1.reelno, M1.frameno, M1.documentid, M1.name, M1.last_update_date, M1.recorded_date, M2.addrline1,

M2.addrline2, M2.city, M2.state, M2.country, M2.postcode

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.

DROP TABLE ptoassignee_current;

CREATE TABLE ptoassignee_current AS

SELECT M1.reelno, M1.frameno, M2.documentid, M1.name, M1.last_update_date, M2.recorded_date, M1.addrline1,

M1.addrline2, M1.country, M1.city, M1.state, M1.postcode

FROM ptoassigneev2 M1, datecheck M2

WHERE (M1.documentid = M2.documentid) AND (M1.recorded_date = M2.recorded_date);

--4994869

These codes should be used to recreate this table using Sonia's updated address information.

===Matching Application and Publication Numbers===

The ptoproperty_cleaned documentids to verify the kind of different patents as specified in the ptoproperty tables.

First the table ptopropertynd was made, including only the distinct documentids in ptoproperty_cleaned.

DROP ptopropertynd;

CREATE TABLE ptopropertynd AS

SELECT DISTINCT * FROM ptoproperty;

--27266638

By creating this table, I also address the duplications caused by the kind XO.

===Final Table (name TBD)===

CREATE TABLE patpub AS

(SELECT ptoproperty_patent.reelno, ptoproperty_patent.frameno, ptoproperty_patent.invention_title,

ptoproperty_patent.patentno, ptoproperty_pub.pubno FROM ptoproperty_patent LEFT JOIN ptoproperty_pub

ON

(ptoproperty_patent.reelno = ptoproperty_pub.reelno) AND (ptoproperty_patent.frameno =

ptoproperty_pub.frameno)

AND (ptoproperty_patent.invention_title = ptoproperty_pub.invention_title)

UNION

SELECT ptoproperty_pub.reelno, ptoproperty_pub.frameno, ptoproperty_pub.invention_title,

ptoproperty_patent.patentno, ptoproperty_pub.pubno FROM ptoproperty_patent RIGHT JOIN ptoproperty_pub

ON

(ptoproperty_patent.reelno = ptoproperty_pub.reelno) AND (ptoproperty_patent.frameno =

ptoproperty_pub.frameno)

AND (ptoproperty_patent.invention_title = ptoproperty_pub.invention_title));

CREATE TABLE patapp AS

(SELECT ptoproperty_patent.reelno, ptoproperty_patent.frameno, ptoproperty_patent.invention_title,

ptoproperty_patent.patentno, ptoproperty_app.appno FROM ptoproperty_patent LEFT JOIN ptoproperty_app

ON

(ptoproperty_patent.reelno = ptoproperty_app.reelno) AND (ptoproperty_patent.frameno =

ptoproperty_app.frameno)

AND (ptoproperty_patent.invention_title = ptoproperty_app.invention_title)

UNION

SELECT ptoproperty_app.reelno, ptoproperty_app.frameno, ptoproperty_app.invention_title,

ptoproperty_patent.patentno, ptoproperty_app.appno FROM ptoproperty_patent RIGHT JOIN ptoproperty_app

ON

(ptoproperty_patent.reelno = ptoproperty_app.reelno) AND (ptoproperty_patent.frameno =

ptoproperty_app.frameno)

AND (ptoproperty_patent.invention_title = ptoproperty_app.invention_title));

==Restructure Address Information (First Stage)==

SQL code and other things are in:

E:/McNair/Projects/PatentAddress/RestructureAddressInfo(First Stage).sql

IO files are on the dbase server in:

Z:/PatentAddress

~~===To do===~~

~~In no particular order:~~

*Remove city, state, zip, country from addrline1 & addrline2 to get clean addrlines.

*Maybe concatenate addrline1 and addrline to make addrline

*Identify clean data (e.g. City that is a city, zip that is a zip, state that is a state)

**By pattern, length, match to list

*Try some more patterns, perhaps with a slightly higher false positive rate, on the remaining uncleaned data

**Iterate!

===Introduction===

SQL code is in: