Changes

[[Category:Internal]]{{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 [[Internal Classification::Legacy| Patent Data (Wiki Page)]].

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_2015 | patentdata --------------+-------------- orgname | assignee city | city country | country patentnumber | patent state | state

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 |

 Patentdata: Column | Type | Modifiers- --------+-------------------+-----------

'''Patent_2015Schema:''' 

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.

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.

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

\COPY patents_merged TO '/tmp/merged_patents_export1.txt' DELIMITER AS E'\t' HEADER NULL AS '' CSV; --COPY 3984771

\COPY patents_merged TO '/tmp/merged_patents_export.txt' DELIMITER AS E'\t' HEADER NULL AS '' CSV; --COPY 1646225

\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 

select count(*) FROM (SELECT DISTINCT patentnumber FROM patents) AS t; --RESULT: 5411151 EXPECTED: 5426566

SELECT COUNT(*), * FROM patents GROUP BY patentnumber,

HAVING COUNT(*) > 1;

SELECT patentnumber, count(*) FROM patents GROUP BY patentnumber HAVING count(*)>1; --7640598

SELECT * FROM patents op WHERE op.patentnumber IN

ORDER BY op.patentnumber;

( SELECT * INTO patentsCleaned FROM patents op WHERE op.patentnumber IN

ORDER BY op.patentnumber ) --SELECT 5191306

INSERT INTO patentsCleaned( SELECT * FROM patents op WHERE op.patentnumber IN

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)

CREATE OR REPLACE FUNCTION cleanpatno (text) RETURNS bigint AS $$

'''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 patent_2015 | patentdata | ---------------------+---------------+ citingpatentnumber | patent | citedpatentnumber | citation | ''' Combined Schema:'''

CREATE TABLE citations Column | Type | Modifiers(--------------------+--------+----------- citingpatentnumber | bigint,| citedpatentnumber | bigint);|

\COPY citations_merged TO '/tmp/merged_citations_export1.txt' DELIMITER AS E'\t' HEADER NULL AS '' CSV; --COPY 38452957

\COPY citations_merged TO '/tmp/merged_citations_export.txt' DELIMITER AS E'\t' HEADER NULL AS '' CSV; -- RESULT : COPY 59227881

\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

ScriptsThe 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.

/* creating patent data tables from '''Script: https://bulkdata.uspto.gov/data2/patent/maintenancefee/*/'''

\COPY CREATE TABLE PatentMaintenanceFee FROM '/bulk/USPTO_Consolidated/MaintFeeEvents_20160613.txt' DELIMITER AS E'\t' HEADER NULL AS '' CSV( patentnumber varchar, applicationnumber int, smallentity varchar, filingdate date, grantissuedate date, maintenancefeedate date, maintenancefeecode varchar );-- RESULT : COPY 14042059

\COPY PatentMaintenanceFee FROM '/* creating tables for historical patent data bulk/USPTO_Consolidated/MaintFeeEvents_20160613.txt' DELIMITER AS E'\t' HEADER NULL AS '' CSV; -- USPTO */RESULT : COPY 14042059

\COPY historicalpatentdata FROM '/bulk/USPTO_Consolidated/HistoricalFiles/historical_masterfile.csv' DELIMITER AS ' CREATE TABLE HistoricalPatentData( applicationid int,' HEADER NULL AS '' CSV 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]]