A presentation I prepared about how to build a patents based indicator (Patents orìiginality) step by step for EPO PATSTAT avoiding most common pitfalls, included commented SQL code
hope you will find it useful
hope you will find it useful
Showing posts with label sql. Show all posts
Showing posts with label sql. Show all posts
Wednesday, September 19, 2018
Friday, September 15, 2017
Patentsview mysql upload scripts
In previous days the new version of Patentsview database has been release
The PatentsView initiative was established in 2012 and is a collaboration between USPTO, US Deptartment of Agriculture (USDA)(1), the Center for the Science of Science and Innovation Policy, New York University, the University of California at Berkeley, Twin Arch Technologies, and Periscopic.
The PatentsView platform is built on a newly developed database that longitudinally links inventors, their organizations, locations, and overall patenting activity. The platform uses data derived from USPTO bulk data files. These data are provided for research purposes and do not constitute the official USPTO record.
From this link you can download my scripts for mysql to upload the new data.
The PatentsView initiative was established in 2012 and is a collaboration between USPTO, US Deptartment of Agriculture (USDA)(1), the Center for the Science of Science and Innovation Policy, New York University, the University of California at Berkeley, Twin Arch Technologies, and Periscopic.
The PatentsView platform is built on a newly developed database that longitudinally links inventors, their organizations, locations, and overall patenting activity. The platform uses data derived from USPTO bulk data files. These data are provided for research purposes and do not constitute the official USPTO record.
From this link you can download my scripts for mysql to upload the new data.
Tuesday, February 18, 2014
Fixing duplicated persons in TLS207
Due to a bug in original data in DOCDB, a percentage of person ids appear twice for the same application.
I pubblish here a code for MySql but easily adaptable to other platforms, for fixing the issue.
The following code creates starting from a list of application ids (XX_appln_id usually I have all EPO applications in it); it must be broken into 3 parts cause in case of duplicates I take the minimum seq_nr (both for applicant and inventor) but in order to avoid cases where we have the same person id as inventor and applicant (so where the min would be 0) in a support table and for the final table I take the max seq_nr.
use newpatstat;
drop table if exists TLS207_pers_appln;
drop table if exists TLS207_pers_appln0;
create table TLS207_pers_appln0
SELECT e.person_id, e.APPLN_ID, min(e.applt_seq_nr) as applt_seq_nr, invt_seq_nr
from patstat.TLS207_pers_appln e
inner join XX_appln_id t on t.appln_id = e .appln_id
where e.invt_seq_nr =0
group by e.person_id, e.APPLN_ID, e.invt_seq_nr ;
insert into TLS207_pers_appln0
SELECT e.person_id, e.APPLN_ID, e.applt_seq_nr, min(e.invt_seq_nr ) as invt_seq_nr
from patstat.TLS207_pers_appln e
inner join XX_appln_id t on t.appln_id = e .appln_id
where e.applt_seq_nr =0
group by e.person_id, e.APPLN_ID, e.applt_seq_nr ;
insert into TLS207_pers_appln0
SELECT e.person_id, e.APPLN_ID, min(e.applt_seq_nr) as applt_seq_nr, min(e.invt_seq_nr) as invt_seq_nr
from patstat.TLS207_pers_appln e
inner join XX_appln_id t on t.appln_id = e .appln_id
group by e.person_id, e.APPLN_ID;
create table TLS207_pers_appln
SELECT e.person_id, e.APPLN_ID, max(e.applt_seq_nr) as applt_seq_nr, max(e.invt_seq_nr ) as invt_seq_nr
from TLS207_pers_appln0 e
group by e.person_id, e.APPLN_ID;
CREATE INDEX IDX207_PERSON ON TLS207_PERS_APPLN(PERSON_ID);
CREATE INDEX IDX207_APPLN ON TLS207_PERS_APPLN(APPLN_ID);
drop table if exists tls207_chk01;
drop table if exists tls207_chk02;
-- two check tables should be empty
create table tls207_chk01
SELECT t.APPLN_ID, t.person_id, invt_seq_nr, Count(t.APPLN_ID) AS cc
FROM tls207_pers_appln t
GROUP BY t.APPLN_ID, t.person_id, invt_seq_nr
HAVING cc > '1';
create table tls207_chk02
SELECT t.APPLN_ID, t.person_id, applt_seq_nr, Count(t.APPLN_ID) AS cc
FROM tls207_pers_appln t
GROUP BY t.APPLN_ID, t.person_id , applt_seq_nr
HAVING cc > '1';
drop table if exists TLS207_pers_appln0;
I pubblish here a code for MySql but easily adaptable to other platforms, for fixing the issue.
The following code creates starting from a list of application ids (XX_appln_id usually I have all EPO applications in it); it must be broken into 3 parts cause in case of duplicates I take the minimum seq_nr (both for applicant and inventor) but in order to avoid cases where we have the same person id as inventor and applicant (so where the min would be 0) in a support table and for the final table I take the max seq_nr.
use newpatstat;
drop table if exists TLS207_pers_appln;
drop table if exists TLS207_pers_appln0;
create table TLS207_pers_appln0
SELECT e.person_id, e.APPLN_ID, min(e.applt_seq_nr) as applt_seq_nr, invt_seq_nr
from patstat.TLS207_pers_appln e
inner join XX_appln_id t on t.appln_id = e .appln_id
where e.invt_seq_nr =0
group by e.person_id, e.APPLN_ID, e.invt_seq_nr ;
insert into TLS207_pers_appln0
SELECT e.person_id, e.APPLN_ID, e.applt_seq_nr, min(e.invt_seq_nr ) as invt_seq_nr
from patstat.TLS207_pers_appln e
inner join XX_appln_id t on t.appln_id = e .appln_id
where e.applt_seq_nr =0
group by e.person_id, e.APPLN_ID, e.applt_seq_nr ;
insert into TLS207_pers_appln0
SELECT e.person_id, e.APPLN_ID, min(e.applt_seq_nr) as applt_seq_nr, min(e.invt_seq_nr) as invt_seq_nr
from patstat.TLS207_pers_appln e
inner join XX_appln_id t on t.appln_id = e .appln_id
group by e.person_id, e.APPLN_ID;
create table TLS207_pers_appln
SELECT e.person_id, e.APPLN_ID, max(e.applt_seq_nr) as applt_seq_nr, max(e.invt_seq_nr ) as invt_seq_nr
from TLS207_pers_appln0 e
group by e.person_id, e.APPLN_ID;
CREATE INDEX IDX207_PERSON ON TLS207_PERS_APPLN(PERSON_ID);
CREATE INDEX IDX207_APPLN ON TLS207_PERS_APPLN(APPLN_ID);
drop table if exists tls207_chk01;
drop table if exists tls207_chk02;
-- two check tables should be empty
create table tls207_chk01
SELECT t.APPLN_ID, t.person_id, invt_seq_nr, Count(t.APPLN_ID) AS cc
FROM tls207_pers_appln t
GROUP BY t.APPLN_ID, t.person_id, invt_seq_nr
HAVING cc > '1';
create table tls207_chk02
SELECT t.APPLN_ID, t.person_id, applt_seq_nr, Count(t.APPLN_ID) AS cc
FROM tls207_pers_appln t
GROUP BY t.APPLN_ID, t.person_id , applt_seq_nr
HAVING cc > '1';
drop table if exists TLS207_pers_appln0;
Thursday, September 9, 2010
code for IPC reclassification in 35 classes
In some previous posts I made available some code about ISI-OST-INPI IPC reclassification in 30 classes.
Recently, in order to have a better balance of the number of patents contained in the different classes, it has been proposed a 35 classes reclassification.
At this link can be consulted the ufficial document from Ulric Schmoch, published from WIPO.
So I made, in order to give am easier format, an excel file containing descriptions in one sheet and IPCs (in the second sheet); the file can be downloaded from this link.
Care the reclassifications sheet contains a colums IPCNOT showing that, if the patent contains such IPCs should be discarded from the class, regardless other IPCs should belong to it.
Differently from ISI-OST-INPI, this reclassification should be run on ALL IPCs (main and secundary).
As the author correctly adresses, this will lead to a 20% of patents belonging to 2 or more classes and for such cases only the main IPC (if available) should be used for reclassifications.
Have fun!!!
Recently, in order to have a better balance of the number of patents contained in the different classes, it has been proposed a 35 classes reclassification.
At this link can be consulted the ufficial document from Ulric Schmoch, published from WIPO.
So I made, in order to give am easier format, an excel file containing descriptions in one sheet and IPCs (in the second sheet); the file can be downloaded from this link.
Care the reclassifications sheet contains a colums IPCNOT showing that, if the patent contains such IPCs should be discarded from the class, regardless other IPCs should belong to it.
Differently from ISI-OST-INPI, this reclassification should be run on ALL IPCs (main and secundary).
As the author correctly adresses, this will lead to a 20% of patents belonging to 2 or more classes and for such cases only the main IPC (if available) should be used for reclassifications.
Have fun!!!
Monday, July 19, 2010
PATSTAT: an assessment on inventors' addresses quality
When trying to consolidate data on inventors in PATSTAT, a part from name, we need some toponomastic data.
An innovator named "J. Smith" would be difficult to connect either with "John Smith" or with "James Smith", even if we have some further data like address, city...
So I investigated with some queries on patstat ediction 10/2009 in what percentuage the fields address, city, zip code and country are filled, by patent office.
I used tables TLS201_APPLN for linking application authority, and TLS206_ASCII (that is the ascii/parsed version of TLS206, included in patstat) for person ids and data.
For selecting distinct person id for inventors (A_I_FLAG = 'I') I used this SQL
Select
T1.APPLN_AUTH, Count(Distinct t6.PERSON_ID)
From
tls201_appln T1 Inner Join tls206_ascii t6
On t6.APPLN_ID = T1.APPLN_ID
Where t6.A_I_FLAG = 'I';
Here below I'm pasting the resulting table for the TOP 20 by inventor count, the full table can be downloaded @ this link.
It might be argued that such a count could have more sense if done by publication authority instead of application authority, since data are taken from search report and it can be noticed that FI data applied @ EPO and published @ WIPO/PCT differ in quality from those applied and published @ EPO.
Maybe it could be topic of a further post...
An innovator named "J. Smith" would be difficult to connect either with "John Smith" or with "James Smith", even if we have some further data like address, city...
So I investigated with some queries on patstat ediction 10/2009 in what percentuage the fields address, city, zip code and country are filled, by patent office.
I used tables TLS201_APPLN for linking application authority, and TLS206_ASCII (that is the ascii/parsed version of TLS206, included in patstat) for person ids and data.
For selecting distinct person id for inventors (A_I_FLAG = 'I') I used this SQL
Select
T1.APPLN_AUTH, Count(Distinct t6.PERSON_ID)
From
tls201_appln T1 Inner Join tls206_ascii t6
On t6.APPLN_ID = T1.APPLN_ID
Where t6.A_I_FLAG = 'I';
Here below I'm pasting the resulting table for the TOP 20 by inventor count, the full table can be downloaded @ this link.
| APPLN_AUTH | inventors | no state | no zip | no country | no address | no city |
| US | 5960856 | 86% | 98% | 21% | 97% | 25% |
| EP | 3705123 | 100% | 100% | 0% | 1% | 1% |
| DE | 2750079 | 100% | 100% | 33% | 100% | 100% |
| JP | 1798271 | 100% | 100% | 98% | 99% | 100% |
| CN | 1537587 | 100% | 100% | 2% | 100% | 100% |
| CA | 1120490 | 100% | 100% | 45% | 100% | 100% |
| AU | 1087573 | 100% | 100% | 98% | 100% | 100% |
| SU | 968915 | 100% | 100% | 41% | 100% | 100% |
| AT | 653048 | 100% | 100% | 29% | 100% | 100% |
| KR | 637296 | 100% | 100% | 14% | 100% | 100% |
| FR | 565254 | 100% | 100% | 98% | 99% | 100% |
| GB | 531087 | 100% | 100% | 70% | 65% | 100% |
| RU | 394691 | 100% | 100% | 29% | 100% | 100% |
| CH | 338739 | 100% | 100% | 11% | 100% | 100% |
| BR | 292047 | 100% | 100% | 89% | 100% | 100% |
| SE | 256248 | 100% | 100% | 85% | 98% | 100% |
| FI | 212722 | 100% | 100% | 11% | 43% | 100% |
| IT | 192460 | 100% | 100% | 74% | 100% | 100% |
| ES | 133471 | 100% | 100% | 17% | 100% | 100% |
| DD | 129845 | 100% | 100% | 7% | 97% | 100% |
It might be argued that such a count could have more sense if done by publication authority instead of application authority, since data are taken from search report and it can be noticed that FI data applied @ EPO and published @ WIPO/PCT differ in quality from those applied and published @ EPO.
Maybe it could be topic of a further post...
Thursday, December 17, 2009
IPC to OST reclassification
After last post I decided to add something of my own, by releasing a conversion table from IPC to OST (7 & 30) reclassification.
The table can be downloaded here : it's a CSV file (separated with semicolon) with an xls file containing class descriptions for 7 & 30 reclassifications.
You will find something like
M_class OST30 OST7
A01B% ; 20 ; 5
A01C% ; 20 ; 5
Where % means in SQL "anything after..."
So you may add your reclassification with an update where YOUR_IPC is like M_CLASS
At the end of the table you will find some IPC without reclassification
C99Z%
H04N101/00
But they are very few in the overall patents population (2 and 328 patents in EPO)
The table can be downloaded here : it's a CSV file (separated with semicolon) with an xls file containing class descriptions for 7 & 30 reclassifications.
You will find something like
M_class OST30 OST7
A01B% ; 20 ; 5
A01C% ; 20 ; 5
Where % means in SQL "anything after..."
So you may add your reclassification with an update where YOUR_IPC is like M_CLASS
At the end of the table you will find some IPC without reclassification
C99Z%
H04N101/00
But they are very few in the overall patents population (2 and 328 patents in EPO)
Subscribe to:
Posts (Atom)