7,612
edits
Changes
Jump to navigation
Jump to search
||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,
Patent Assignment Data Restructure (view source)
Revision as of 13:41, 21 September 2020
, 13:41, 21 September 2020no edit summary
{{Project|Has project output=Data|Has sponsor=McNair ProjectsCenter
|Has title=Patent Data Restructure
|Has owner=Marcela Interiano, Sonia Zhang,
|Has start date=201701
|Has deadline=201705
}}
In order to restructure the current patent dataset, the data requires rigorous cleaning. The primary areas for improvement are:
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===
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)===
SQL code and other things are in:
E:/McNair/Projects/PatentAddress/RestructureAddressInfo(First Stage).sql
IO files are on the dbase server in:
ptoassigneend_us_identify_subtotal | 3195769
----------------------------------------------------|-------------
ptoassigneend_us_candid1 (city and state is are clean) | 136958
ptoassigneend_us_candid2 (postcode is clean) | 184123
====Output: ptoassigneend_us_identify_subtotal====
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 records in ptoassigneend_allus. 10.5% left in ptoassigneend_us_temp5.
Note: About 60 records are missing. For example, 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
