Changes

Jump to navigation Jump to search

Patent Data Processing - SQL Steps (view source)

Revision as of 13:34, 21 September 2020

1,701 bytes removed ,  13:34, 21 September 2020
no edit summary
{{Project
|Has project output=
|Has sponsor=McNair Center
|Has title=Patent Data Processing - SQL Steps
|Has owner=Shelby Bice,
|Has project status=Subsume
|Has keywords=Tool
}}
Return to [[Patent Data (Wiki Page)]].
Patentdata:== Objective ==Column | Type | Modifiers---------+-------------------+----------The McNair Center owns two sets of patent data -one set that is inherited from Harvard, the Harvard dataverse, which is stored in the database patentdata and another that is generated by crawlers pulling data from the USPTO website, which is stored in the database '''patent_2015'''. patent | integer | kind | character varying | claims | integer | apptype | integer | appnum | integer | gdate | date | gyear | integer | appdate | date | appyear | integer |We are now merging and cleaning the two data sets, and storing them in a schema that is amalgamation of the two underlying schema for the citations tables, assignees tables, and patents tables. The destination schema is '''allpatent'''.
Column Names: patent int, kind varchar, claims int, apptype int, appnum int, gdate date, gyear int, appdate date, appyear int== Assignees Data==
The schema for the assignees table in '''patentdata''' database is:
Column | Type | Modifiers
-------------+-------------------+-----------
patent | integer |
asgtype | integer |
assignee | character varying |
city | character varying |
state | character varying |
country | character varying |
nationality | character varying |
residence | character varying |
asgseq | integer |
Patent_2015The schema for the assignees table in patent_2015 is : Column | Type | Modifiers -------------------------------+---------+----------- patentnumber | int | not null kind | varchar | grantdate | date | type | varchar | applicationnumber | varchar | filingdate | date | prioritydate | date | prioritycountry | varchar | prioritypatentnumber | varchar | ussubclass | varchar | maingroup | varchar | subgroup | varchar | cpcsubclass | varchar | cpcmaingroup | varchar | cpcsubgroup | varchar | classificationnationalcountry | varchar | classificationnationalclass | varchar | title | varchar | numberofclaims | int | primaryexaminerfirstname | varchar | primaryexaminerlastname | varchar | primaryexaminerdepartment | varchar | pctpatentnumber | varchar | filename | varchar | Column Names: patentnumber int, kind varchar, grantdate date, type varchar, applicationnumber varchar, filingdate date, prioritydate date, prioritycountry varchar, prioritypatentnumber varchar, ussubclass varchar, maingroup varchar, subgroup varchar, cpcsubclass varchar, cpcmaingroup varchar, cpcsubgroup varchar, classificationnationalcountry varchar, classificationnationalclass varchar, title varchar, numberofclaims int, primaryexaminerfirstname varchar, primaryexaminerlastname varchar, primaryexaminerdepartment varchar, pctpatentnumber varchar, filename varchar
Combined Schema: Column | Type | Modifiers --------------+---------+----------- lastname | text | firstname | text | orgname | text | city | text | country | text | patentcountry | text | patentnumber | integer | state | text | address | text | postcode | text |
Column Names: patent intTo merge both schemas, kind varcharwe have some columns that overlap, claims int, apptype int, appnum int, gdate date, gyear int, appdate date, appyear intand some columns that don't.
'''Overlapping Columns'''
patentnumber int, patent_2015 | patentdata -------------- patent kind varchar, +-- kind grantdate date, --gdate type varchar, applicationnumber varchar, filingdate date, prioritydate date, prioritycountry varchar, prioritypatentnumber varchar, ussubclass varchar, maingroup varchar, subgroup varchar, cpcsubclass varchar, cpcmaingroup varchar, cpcsubgroup varchar, classificationnationalcountry varchar, classificationnationalclass varchar, title varchar, numberofclaims int, primaryexaminerfirstname varchar, primaryexaminerlastname varchar, primaryexaminerdepartment varchar, pctpatentnumber varchar, filename varchar claims int, apptype int, appnum int, gyear int, appdate date, appyear int Output Schema: patents CREATE TABLE patents_merged( patentnumber int, kind varchar, grantdate date, type varchar, applicationnumber varchar, filingdate date, prioritydate date, prioritycountry varchar, prioritypatentnumber varchar, ussubclass varchar, maingroup varchar, subgroup varchar, cpcsubclass varchar, cpcmaingroup varchar, cpcsubgroup varchar, classificationnationalcountry varchar, classificationnationalclass varchar, title varchar, numberofclaims int, primaryexaminerfirstname varchar, primaryexaminerlastname varchar, primaryexaminerdepartment varchar, pctpatentnumber varchar, filename varchar, claims int, apptype int, appnum int, gyear int, appdate date, appyear int ); patentdata: INSERT INTO patents_merged ( SELECT patent, kind, gdate, 'NULL', 'NULL', NULL, NULL, 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', -1, 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', claims, apptype, appnum, gyear, appdate, appyear FROM patents ); -- RESULT : INSERT 0 3984771 patent_2015: INSERT INTO patents_merged ( SELECT patentnumber, kind, grantdate, type, applicationnumber, filingdate, prioritydate, prioritycountry, prioritypatentnumber, ussubclass, maingroup, subgroup, cpcsubclass, cpcmaingroup, cpcsubgroup, classificationnationalcountry, classificationnationalclass, title, numberofclaims, primaryexaminerfirstname, primaryexaminerlastname, primaryexaminerdepartment, pctpatentnumber, filename, -1, -1, -1, -1, NULL, -1 FROM patents ); -- RESULT : INSERT 0 1646225 orgname | assignee COPY SCRIPTS: city | city patentdata: country | country\COPY patents_merged TO '/tmp/merged_patents_export1.txt' DELIMITER AS E'\t' HEADER NULL AS '' CSV; patentnumber | patent --COPY 3984771 state | state
patent_2015:\COPY patents_merged TO '/tmp/merged_patents_exportThese columns will have entries for most rows in the table, because they exist in both tables. The rest of the columns will be populated based on which table the row is coming from.txt' DELIMITER AS E'\t' HEADER NULL AS '' CSV;--COPY 1646225
PATENTS TABLE\COPY patents FROM '/tmp/merged_patents_export1.txt' DELIMITER AS E'\tFinal Schema' HEADER NULL AS '' CSV;-- RESULT : COPY 3984771\COPY patents FROM '/tmp/merged_patents_export.txt' DELIMITER AS E'\t' HEADER NULL AS '' CSV;-- RESULT : COPY 1646225
Table "public.assignees"
Column | Type | Modifiers
---------------+-------------------+-----------
lastname | character varying |
firstname | character varying |
address | character varying |
postcode | character varying |
orgname | character varying |
city | character varying |
country | character varying |
patentnumber | integer |
state | character varying |
patentcountry | character varying |
nationality2 | character varying |
residence | character varying |
asgseq | integer |
asgtype | integer |
TESTING:select count(*) FROM (SELECT DISTINCT patentnumber FROM patents) AS t;'''Non--RESULT: 5411151overlapping Columns'''EXPECTEDThese are the columns that belong to either one of the assignees tables, and not to both. For these cases, to help users understand where the row is coming from, the following insert rules have been followed: 5426566
SELECT COUNT(*)For columns of type int, insert -1*FROM patentsGROUP BY patentnumber, kind, grantdate, For columns of type, applicationnumber, filingdate, prioritydate, prioritycountry, prioritypatentnumber, ussubclass, maingroup, subgroup, cpcsubclass, cpcmaingroup, cpcsubgroup, classificationnationalcountry, classificationnationalclass, title, numberofclaims, primaryexaminerfirstname, primaryexaminerlastname, primaryexaminerdepartment, pctpatentnumber, filename, claims, apptype, appnum, gyear, appdate, appyearHAVING COUNTstring (*character varying) > 1;, the string 'null' has been inserted.
SELECT patentnumberTherefore, count(*)FROM patentsGROUP BY patentnumberHAVING count(*)>1;--7640598 if a row has appropriate values for orgname, state, city ,etc, but 'null' values for lastname, firstname, address and postcode, the row has come from the patentdata table.
==== Index ====
Since the table is relatively large, and is likely to be searched often, an index has been imposed on the table.
SELECT * FROM patents opWHERE op.patentnumber IN ( SELECT ip.patentnumber FROM patents ip GROUP BY ip.patentnumber HAVING COUNT allpatent=# CREATE INDEX ON assignees (*)>1 orgname);ORDER BY op.patentnumber; CREATE INDEX
(SELECT * ====Sample insert and copy commands ==== INSERT INTO patentsCleanedFROM patents opWHERE op.patentnumber INassignees_merge
(
SELECT ip 'null', 'null', 'null', 'null', a.assignee, a.city, a.patentnumber country, FROM patents ipa.patent, a.state, 'null', GROUP BY ipa.patentnumbernationality, HAVING COUNT(*)=1a.residence, )a.asgseq,ORDER BY op a.patentnumberasgtype FROM assignees a )--SELECT 5191306;
  INSERT INTO patentsCleaned(SELECT * FROM patents opWHERE op.patentnumber INassignees_merge
(
SELECT ip assignees.lastname, assignees.firstname, assignees.address, assignees.postcode, assignees.orgname, assignees.patentnumber city, FROM patents ipassignees.country, GROUP BY ipassignees.patentnumber, HAVING COUNT(*)>1assignees.state, assignees.patentcountry, )'null',AND op.applicationnumber NOT LIKE 'NULLnull', -1, -1ORDER BY op.patentnumber FROM assignees );
--219845
TESTING: \COPY assignees_merge TO '/tmp/assignees_merge_export.txt' DELIMITER AS E'\t' HEADER NULL AS '' CSV;allpatent=# select count(*) from patentsCleaned \COPY assignees FROM '/tmp/assignees_merge_export.txt' DELIMITER AS E'\t' HEADER NULL AS '' CSV; count-- ------- 5411151(1 row)1607724
allpatent=# select count(*), patentnumber \COPY assignees_merge TO '/tmp/assignees_merge_export1.txt' DELIMITER AS E'\t' HEADER NULL AS '' CSV; \COPY assignees FROM patentsCleaned group by patentnumber having count(*) > 1'/tmp/assignees_merge_export1.txt' DELIMITER AS E'\t' HEADER NULL AS '' CSV; count | patentnumber-------+ --------------(0 rows)3818842
Note : The assignees table was updated on 6/23 to remove the 'null' string and the '-1' values.
********** INDEX CREATION **************==Patents ==
ALTER TABLE patents ADD PRIMARY KEY (patentnumber);-- RESULT '''Patentdata Schema: ALTER TABLEallpatent=# CREATE UNIQUE INDEX patent_idx ON patents (patentnumber);'''
allpatent=# CREATE INDEX ON assignees (orgname);CREATE INDEX      **Patents Patentdata: Column | Type | Modifiers- --------+-------------------+-----------
patent | integer |
kind | character varying |
appdate | date |
appyear | integer |
 
Column Names:
patent int,
kind varchar,
claims int,
apptype int,
appnum int,
gdate date,
gyear int,
appdate date,
appyear int
'''Patent_2015 Schema:'''
Patent_2015:
Column | Type | Modifiers
-------------------------------+---------+-----------
filename | varchar |
''' Overlapping Columns ''' patent_data patent_2015 Column Names:--------------+------------- patent | patentnumber int, kind varchar, | kind grantdate date,claims | numberofclaims apptype | type varchar, appnum | applicationnumber varchar, filingdate date,gdate | grantdate prioritydate date,appdate prioritycountry varchar,| filingdate prioritypatentnumber varchar, ussubclass varchar, maingroup varchar, subgroup varchar, cpcsubclass varchar, cpcmaingroup varchar, cpcsubgroup varchar, classificationnationalcountry varchar, classificationnationalclass varchar, title varchar, numberofclaims int, primaryexaminerfirstname varchar, primaryexaminerlastname varchar, primaryexaminerdepartment varchar, pctpatentnumber varchar, filename varchar'''Combined Schema:'''
Combined SchemaThe final schema of the patents table is :
Column Names: | Type | Modifiers ----------------------+-------------------+----------- patent int, | integer | not null grantdate | date | prioritydate | date | prioritycountry | character varying | prioritypatentnumber | character varying | cpcsubgroup | character varying | kind varchar, pctpatentnumber | character varying | claims int, | integer | apptype int, appnum int, | integer | gdate date, gyear int, | integer | appdate | date, | appyear | integer | nber | integer | uspc | character varying | uspc_sub | character varying | appyear intFrom the total list of columns belonging to both the tables (patentdata and patent_2015), a few columns, most of them related to classification of patents, have been dropped since the data in the tables was not clean.
Additionally, three columns - nber, uspc, uspc_sub have been added from the historicalpatentdata, a table built from data downloaded from the USPTO Bulk Data Storage. The join was executed on the patent number.
patentnumber int, -- patent kind varchar, -- kind grantdate date Note : The addition, deletion of columns as through separate [[Patent Data Cleanup --gdate type varchar, applicationnumber varchar, filingdate date, prioritydate date, prioritycountry varchar, prioritypatentnumber varchar, ussubclass varchar, maingroup varchar, subgroup varchar, cpcsubclass varchar, cpcmaingroup varchar, cpcsubgroup varchar, classificationnationalcountry varchar, classificationnationalclass varchar, title varchar, numberofclaims int, primaryexaminerfirstname varchar, primaryexaminerlastname varchar, primaryexaminerdepartment varchar, pctpatentnumber varchar, filename varchar claims int, apptype int, appnum int, gyear int, appdate date, appyear int Output Schema: patents CREATE TABLE patents_merged( patentnumber int, kind varchar, grantdate date, type varchar, applicationnumber varchar, filingdate date, prioritydate date, prioritycountry varchar, prioritypatentnumber varchar, ussubclass varchar, maingroup varchar, subgroup varchar, cpcsubclass varchar, cpcmaingroup varchar, cpcsubgroup varchar, classificationnationalcountry varchar, classificationnationalclass varchar, title varchar, numberofclaims int, primaryexaminerfirstname varchar, primaryexaminerlastname varchar, primaryexaminerdepartment varchar, pctpatentnumber varchar, filename varchar, claims int, apptype int, appnum int, gyear int, appdate dateJune 2016 |scripts]], appyear int );therefore the scripts below will be slightly discrepant.
==== Index and Key Creation ====Patent numbers are distinct in this table, and are central to the rest of the fields in the table. A primary key can therefore be imposed on the column. Also, since a number of searches are likely to be conducted on this table, an index has been imposed as well. Code: ALTER TABLE patents ADD PRIMARY KEY (patentnumber); -- RESULT : ALTER TABLE allpatent=# CREATE UNIQUE INDEX patent_idx ON patents (patentnumber);   ====Sample Insert and Copy Statements====
patentdata:
INSERT INTO patents_merged
(
SELECT patent, kind, gdate, 'NULL', 'NULL', NULL, NULL, 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', -1, 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', claims, apptype, appnum, gyear, appdate, appyear FROM patents ); -- RESULT : INSERT 0 3984771
patent_2015:
COPY SCRIPTS:
patentdata:
\COPY patents_merged TO '/tmp/merged_patents_export1.txt' DELIMITER AS E'\t' HEADER NULL AS '' CSV; --COPY 3984771
patent_2015:
\COPY patents_merged TO '/tmp/merged_patents_export.txt' DELIMITER AS E'\t' HEADER NULL AS '' CSV; --COPY 1646225
PATENTS TABLE
\COPY patents FROM '/tmp/merged_patents_export1.txt' DELIMITER AS E'\t' HEADER NULL AS '' CSV; -- RESULT : COPY 3984771 \COPY patents FROM '/tmp/merged_patents_export.txt' DELIMITER AS E'\t' HEADER NULL AS '' CSV; -- RESULT : COPY 1646225
====TESTING ====
select count(*) FROM (SELECT DISTINCT patentnumber FROM patents) AS t;
--RESULT: 5411151
EXPECTED: 5426566
TESTING:select count(*) FROM (SELECT DISTINCT patentnumber FROM patents) AS t;--RESULT: 5411151EXPECTED: 5426566We found some copies of a few rows, where both the patent_2015 and patentdata
SELECT COUNT(*), * FROM patents GROUP BY patentnumber,
kind,
grantdate,
appdate,
appyear
HAVING COUNT(*) > 1;
SELECT patentnumber, count(*) FROM patents GROUP BY patentnumber HAVING count(*)>1; --7640598
SELECT * FROM patents op WHERE op.patentnumber IN
(
SELECT ip.patentnumber
HAVING COUNT(*)>1
)
ORDER BY op.patentnumber;
( SELECT * INTO patentsCleaned FROM patents op WHERE op.patentnumber IN
(
SELECT ip.patentnumber
HAVING COUNT(*)=1
)
ORDER BY op.patentnumber ) --SELECT 5191306
INSERT INTO patentsCleaned( SELECT * FROM patents op WHERE op.patentnumber IN
(
SELECT ip.patentnumber
HAVING COUNT(*)>1
)
AND op.applicationnumber NOT LIKE 'NULL' ORDER BY op.patentnumber );
--219845
====TESTING:==== allpatent=# select count(*) from patentsCleaned; count --------- 5411151 (1 row)
allpatent=# select count(*), patentnumber FROM patentsCleaned group by patentnumber having count(*) > 1; count | patentnumber -------+-------------- (0 rows)
== Citations==
********** INDEX CREATION ************** ALTER TABLE patents ADD PRIMARY KEY In the citations table, we needed to define another function that would convert a textual patent number into a number (patentnumber);-- RESULT : ALTER TABLEallpatent=# CREATE UNIQUE INDEX patent_idx ON big int, since the patents (patentnumber); allpatent=# CREATE INDEX ON assignees (orgnamenumber were exceeding the range of regular integers.);CREATE INDEX     **Citations
To Extract Patents with Numbers Only and to Ignore Other RegExes
CREATE OR REPLACE FUNCTION cleanpatno (text) RETURNS bigint AS $$
if ($_[0]) {
my $var=$_[0];
************ Columns *********************
patentdata:
Column | Type | Modifiers
-------------+-------------------+-----------
patent | integer |
cit_date | date |
cit_name | character varying |
cit_kind | character varying |
cit_country | character varying |
citation | integer |
category | character varying |
citseq | integer |
'''patentdata schema:'''  Column | Type | Modifiers ------------+-------------------+----------- patent | integer | cit_date | date | cit_name | character varying | cit_kind | character varying | cit_country | character varying | citation | integer | category | character varying | citseq | integer |   SELECT patent as citingpatentnumber, citation AS citedpatentnumber INTO citations_merged FROM citations; --SELECT 38452957
'''patent_2015schema:''' Column | Type | Modifiers ---------------------+---------+----------- citingpatentnumber | integer | citingpatentcountry | text | citedpatentnumber | text | citedpatentcountry | text |
SELECT CAST(citingpatentnumber AS bigint), CAST(cleanpatno( citedpatentnumber) AS bigint) as citedpatentnumber INTO citations_merged FROM citations; -- RESULT : SELECT 59227881
FINAL TABLE:'''Overlapping Columns'''
CREATE TABLE citations patent_2015 | patentdata |( ---------------------+---------------+ citingpatentnumber | patent | citedpatentnumber | citation | ''' Combined Schema:'''  Column | Type | Modifiers--------------------+--------+----------- citingpatentnumber | bigint,| citedpatentnumber | bigint);|
patentdata:
\COPY citations_merged TO '/tmp/merged_citations_export1.txt' DELIMITER AS E'\t' HEADER NULL AS '' CSV; --COPY 38452957
patent_2015:
\COPY citations_merged TO '/tmp/merged_citations_export.txt' DELIMITER AS E'\t' HEADER NULL AS '' CSV; -- RESULT : COPY 59227881
allpatent:
\COPY citations FROM '/tmp/merged_citations_export.txt' DELIMITER AS E'\t' HEADER NULL AS '' CSV; --RESULT : COPY 59227881
\COPY citations FROM '/tmp/merged_citations_export1.txt' DELIMITER AS E'\t' HEADER NULL AS '' CSV; --RESULT: COPY 38452957
CLONING:
CREATE DATABASE allpatentsProcessed WITH TEMPLATE allpatent OWNER researcher;
 
== USPTO Consolidated Patent Data ==
 
The USPTO has a repository of patent data on their Bulk Data Storage system. We have this data downloaded and loaded into a table on the patent database. Here are the steps followed:
* Download file from the BDS system - we have access to CSV files.
* Create table with required specs
* use the \COPY command to copy the data from the file into the table.
 
Script follows.
 
'''Script:'''
 
/* creating patent data tables from : https://bulkdata.uspto.gov/data2/patent/maintenancefee/*/
 
CREATE TABLE PatentMaintenanceFee(
patentnumber varchar,
applicationnumber int,
smallentity varchar,
filingdate date,
grantissuedate date,
maintenancefeedate date,
maintenancefeecode varchar
);
 
\COPY PatentMaintenanceFee FROM '/bulk/USPTO_Consolidated/MaintFeeEvents_20160613.txt' DELIMITER AS E'\t' HEADER NULL AS '' CSV;
-- RESULT : COPY 14042059
 
/* creating tables for historical patent data - USPTO */
 
CREATE TABLE HistoricalPatentData(
applicationid int,
pubno varchar,
patentnumber varchar,
NBER int,
USPC varchar,
USPC_sub varchar,
applicationdate date,
prioritydate date,
pubdate date,
displaydate date,
disptype varchar,
exp_dt date,
exp_dt_max date,
pta int
);
 
\COPY historicalpatentdata FROM '/bulk/USPTO_Consolidated/HistoricalFiles/historical_masterfile.csv' DELIMITER AS ',' HEADER NULL AS '' CSV;
 
--COPY 11191813
 
[[Category:Internal]]
[[Internal Classification::Legacy| ]]
[[Category:Patent]]
Retrieved from "http://www.edegan.com/wiki/Special:MobileDiff/2778...27529"

Navigation menu