View source for Patent Data Cleanup (June 2016)

== About this Page ==

This page contains the script that was used to clean up the patents and assignees tables in allpatent.

== Script ==
 ALTER TABLE patents
 RENAME COLUMN patentnumber TO patent;

 ALTER TABLE patents
  DROP COLUMN kind,
  DROP COLUMN title,
  DROP COLUMN ussubclass, **
  DROP COLUMN maingroup, --
  DROP COLUMN subgroup,--
  DROP COLUMN cpcsubclass, ++
  DROP COLUMN cpcmaingroup, ++
  DROP COLUMN classificationnationalcountry,
  DROP COLUMN classificationnationalclass,** (?)
  DROP COLUMN primaryexaminerfirstname,
  DROP COLUMN primaryexaminerlastname,
  DROP COLUMN primaryexaminerdepartment,
  DROP COLUMN filename;

 UPDATE patents
  SET type = '2015'
  WHERE type != 'NULL';

 -- RESULT : UPDATE 1646225

 UPDATE patents
  SET type = '2010'
  WHERE type = 'NULL';
 -- RESULT : UPDATE 3764926

 /* Join the historical patent data from the US PTO with the patents table */

 ALTER TABLE PATENTS
  ADD COLUMN nber INT,
  ADD COLUMN uspc varchar,
  ADD COLUMN uspc_sub varchar;

 UPDATE patents p
  SET nber = hp.nber,
  uspc = hp.uspc,
  uspc_sub = hp.uspc
   FROM historicalpatentdata hp
   WHERE hp.patentnumber = CAST(p.patent AS varchar);

 -- RESULT : UPDATE 5113655

 /* Mergeing some columns - claims and number of claims - column name : claims*/
 UPDATE patents
  SET claims = numberofclaims
  WHERE claims = -1;
 -- RESULTS : UPDATE 1646225

 /* Merging columns - 
 UPDATE patents
  SET appnum = CAST (applicationnumber AS INT)
  where appnum = -1;
 -- RESULT : UPDATE 1646225

 UPDATE patents
  SET appdate = filingdate
  where appdate = '0001-01-01 BC'
  OR filingdate is not NULL;

 -- RESULT UPDATE 1646225

 ALTER TABLE patents
  DROP COLUMN apptype;

 /* Generating GYear and AppYear from the dates */
 UPDATE patents
  SET gyear = EXTRACT(year from grantdate)
  WHERE gyear = -1
  AND grantdate IS NOT NULL;
 UPDATE 1646225

 UPDATE patents
  SET appyear = EXTRACT(year from appdate)
  WHERE appyear = -1 
  AND appdate is not null;
 -- RESULT UPDATE 1646225

 /* Test Script */
 SELECT patentnumber, ussubclass, maingroup, subgroup, cpcsubclass, cpcmaingroup, cpcsubgroup, classificationnationalcountry, classificationnationalclass FROM Patents LIMIT 100;

 patent               | integer           | not null
  grantdate            | date              |
  type                 | character varying |
  applicationnumber    | character varying |
  filingdate           | date              |
  prioritydate         | date              |
  prioritycountry      | character varying |
  prioritypatentnumber | character varying |
  cpcsubgroup          | character varying |
  numberofclaims       | integer           |
  pctpatentnumber      | character varying |
  claims               | integer           |
  appnum               | integer           |
  gyear                | integer           |
  appdate              | date              |
  appyear              | integer           |
  nber                 | integer           |
  uspc                 | character varying |
  uspc_sub             | character varying 

 /* Drop the merged columns */

 ALTER TABLE patents
  DROP COLUMN numberofclaims,
  DROP COLUMN filingdate,
  DROP COLUMN applicationnumber,
  DROP COLUMN type;


 UPDATE patents
  SET prioritycountry = NULL
  WHERE prioritycountry = 'NULL';

 UPDATE patents
  SET pctpatentnumber = NULL
  WHERE pctpatentnumber = 'NULL';

 UPDATE patents
  SET prioritypatentnumber = NULL
  WHERE prioritypatentnumber = 'NULL';

 UPDATE patents
  SET cpcsubgroup = NULL
  WHERE cpcsubgroup = 'NULL';

 UPDATE patents
  SET appnum = NULL
  WHERE appnum = -1;

 UPDATE patents
  SET gyear = NULL
  WHERE gyear = -1;

 UPDATE patents

  SET appyear = NULL
  WHERE appyear = -1;

 Results:

 allpatent=# ALTER TABLE patents
 allpatent-# DROP COLUMN numberofclaims,
 allpatent-# DROP COLUMN filingdate,
 allpatent-# DROP COLUMN applicationnumber,
 allpatent-# DROP COLUMN type;
 ALTER TABLE
 allpatent=#
 allpatent=#
 allpatent=# UPDATE patents
 allpatent-# SET prioritycountry = NULL
 allpatent-# WHERE prioritycountry = 'NULL';
 ^[[BUPDATE 3764926
 allpatent=#
 allpatent=# UPDATE patents
 allpatent-# SET pctpatentnumber = NULL
 allpatent-# WHERE pctpatentnumber = 'NULL';
 UPDATE 3764926
 allpatent=#
 allpatent=# UPDATE patents
 allpatent-# SET prioritypatentnumber = NULL
 allpatent-# WHERE prioritypatentnumber = 'NULL';
 UPDATE 3764926
 allpatent=#
 allpatent=# UPDATE patents
 allpatent-# SET cpcsubgroup = NULL
 allpatent-# WHERE cpcsubgroup = 'NULL';
 UPDATE 3764926
 allpatent=#
 allpatent=# UPDATE patents
 allpatent-# SET appnum = NULL
 allpatent-# WHERE appnum = -1;
 UPDATE 0
 allpatent=#
 allpatent=# UPDATE patents
 allpatent-# SET gyear = NULL
 allpatent-# WHERE gyear = -1;
 UPDATE 0
 allpatent=#
 allpatent=# UPDATE patents
 allpatent-# SET appyear = NULL
 allpatent-# WHERE appyear = -1;
 UPDATE 0
 allpatent=#


 UPDATE assignees
  SET lastname = NULL
  WHERE lastname = 'null';

 UPDATE assignees
  SET firstname = NULL
  WHERE firstname = 'null';

 UPDATE assignees
  SET address = NULL
  WHERE address = 'null';

 UPDATE assignees
  SET postcode = NULL
  WHERE   postcode = 'null';

 UPDATE assignees
  SET patentcountry = NULL
  WHERE patentcountry = 'null';

 UPDATE assignees
  SET nationality2 = NULL
  WHERE nationality2 = 'null';

 UPDATE assignees
  SET residence = NULL
  WHERE residence = 'null';

 UPDATE assignees
  SET asgseq = NULL
  WHERE asgseq= -1;

 UPDATE assignees
  SET asgtype = NULL
  WHERE asgtype = -1;

 RESULTS:

 UPDATE assignees
 allpatent-# SET lastname = NULL
 allpatent-# WHERE lastname = 'null';

 UPDATE 3818842
 allpatent=#
 allpatent=# UPDATE assignees
 allpatent-# SET firstname = NULL
 allpatent-# WHERE firstname = 'null';
 UPDATE 3818842
 allpatent=#
 allpatent=# UPDATE assignees
 allpatent-# SET address = NULL
 allpatent-# WHERE address = 'null';
 UPDATE 3818842
 allpatent=#
 allpatent=# UPDATE assignees
 allpatent-# SET postcode = NULL
 allpatent-# WHERE   postcode = 'null';
 UPDATE 3818842
 allpatent=#
 allpatent=# UPDATE assignees
 allpatent-# SET patentcountry = NULL
 allpatent-# WHERE patentcountry = 'null';
 UPDATE 3818842
 allpatent=#
 allpatent=# UPDATE assignees
 allpatent-# SET nationality2 = NULL
 allpatent-# WHERE nationality2 = 'null';
 UPDATE 1607714
 allpatent=#
 allpatent=# UPDATE assignees
 allpatent-# SET residence = NULL
 allpatent-# WHERE residence = 'null';
 UPDATE 1607714
 allpatent=#
 allpatent=# UPDATE assignees
 allpatent-# SET asgseq = NULL
 allpatent-# WHERE asgseq= -1;
 UPDATE 1607714
 allpatent=#
 allpatent=# UPDATE assignees
 allpatent-# SET asgtype = NULL
 allpatent-# WHERE asgtype = -1;
 UPDATE 1607714
 allpatent=#


 CREATE DATABASE allpatent_clone WITH TEMPLATE allpatent OWNER dbuser;