1,181
edits
Changes
Jump to navigation
Jump to search
Patent Assignment Data Restructure (view source)
Revision as of 15:25, 3 May 2017
, 15:25, 3 May 2017→Data Cleanup Progress
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===
