PATSTAT 2019b MySQL upload scripts

At this link are available my new scripts for MySQL to upload PATSTAT 2019b data.


Only change in this data ediction is the removal of table TLS906_PERSON that is fully integrated in TLS206.
My scripts have been updated to remove MyIsam as default engine and to make them fully compatible with MySQL 8.0.

PATSTAT & Patentsview: complements or substitutes?

Match of PATSTAT data (2019 spring) and PatentsView (jan 2019) is discussed here, with focus on how this match can help to enrich PATSTAT data with information not contained in USPTO patents (and the other way round).

PATSTAT & Patentsview: complements or substitutes? from Gianluca Tarasconi

Patents applicants: how to create the full time series

I share a presentation I made @ EPo & KUL summer school in Vienna last september!
hope it's useful to someone else.

Patents change applicants data within time;
Main reason for change are ownership change, name/address change, M&A …
Applicant’s names contained in TLS206 is the ‘last available’ data;
PATSTAT Global + EP Register make available several sources to build a chain of names
and a timeline for patents contained;

Patents applicants: how to create the full time series from Gianluca Tarasconi

Patents citations from Pubmed Scientific publications

A recently released dataset from Marx, Matt; Aaron Fuegi contains citations from USPTO patents granted 1947-2018 to articles captured by the MS Academic Graph (ID) from 1800-2018.

Files, tab-separated, are available at link : https://zenodo.org/record/3338601

The main file, pcs.tsv, contains the resolved citations matching patent number, MAG ID, the original citation from the patent, an indicator for whether the citation was supplied by the applicant, examiner, or unknown, and a confidence score (1-10) indicating how likely this match is correct.

There is also a PubMed-specific match in pcs-pubmed.tsv.

Authors also made availabel source code for generating the patent citations to science in pcs.tsv is available at https://github.com/mattmarx/reliance_on_science. Source code for generating jif.zip and jcif.zip (Journal Impact Factor and Journal Commercial Impact Factor) is at https://github.com/mattmarx/jcif.

Scripts and programs are mainly for stata & linux (DO files and sh scripts).

 

PATSTAT - patentsview concordance update 2019

PatentsView  is a platform built on data derived from USPTO bulk data files.

This dataset complements perfectly PATSTAT since the former has an native disambiguation of inventors and applicants and a geocoding system applied to inventors and applicants, while the latter links US data to other offices, allowing to calculate knowledge spillovers, family data etc.

At this link is possible to download a table of concordance between patent_id (Patentsview main key) and appln_id (from PATSTAT).

Overlapping of the two datasets is not perfect since Patentsview contains only granted patents after 1975, where PATSTAT has also application (ungranted) and timeframe covers also pre-1975 data.
On the other hand PATSTAT misses design patents before 2001,  plants before 2001 and 'statutory invention registration' type of patent.

Data are from PATSTAT spring 2019 and Patentsview march 2019, thus also 2019 data in PW are partially missing in PATSTAT.

Patent familiarity calc scripts

I made available on Github a set of MySQL and python scripts to create familiarity indicator by IPC class, on NBER patents dataset.

https://github.com/gtarasconi/NBER-familiarity-indicators

Inventor’s familiarity with components of the invention measured by the (a) recent and (b) frequent usage of focal patent’s classes across all US patents. Thus, we calculate a measure of familiarity for each separate class of a focal patent. Therefore, the more recently and frequently a class has been used, its individual measure will be higher.

Based on Fleming 2001
https://funginstitute.berkeley.edu/wp-content/uploads/2012/10/Recombinant-Uncertainty-in-Technological-Search.pdf

PATSTAT 2019a analysis of changes

As Stated in EPO documents new version of PATSTAT comes with 2 mayor changes:

Person names in the original language (PERSON_NAME_ORIG_LG)
Field added in tables TLS206, 226, 906. This creates an inflation of records and lack of retrocompatibility with old personwise data.

“RELEVANT_CLAIM” attribute in the TLS215_CITN_CATEG table
A new attribute “RELEVANT_CLAIM” has been added to the TLS215_CITN_CATEG table. This attribute contains a single number referring to the claim to which the citation refers to.

About first change you can find below, for offices with more than 100.000 person_ids, how are they  most affected, where the last column shows inflation rate

APPLN_AUTH	Count_person_id_2018b	Count_person_id_2019a	ratio
AR	150688	182005	121%
AT	1142798	1232406	108%
AU	1870980	2008555	107%
BE	196598	204115	104%
BR	1223470	1315337	108%
CA	2524565	2675965	106%
CH	650912	664626	102%
CN	6192040	9956730	161%
CS	166131	178125	107%
CZ	130967	233777	179%
DD	226783	234141	103%
DE	5095800	5258606	103%
DK	624763	687156	110%
EP	6624815	6625117	100.005%
ES	1291041	1389533	108%
FI	365453	384041	105%
FR	1471641	1588271	108%
GB	1757273	1797304	102%
GR	134008	173190	129%
HK	300604	426287	142%
HU	234866	303144	129%
IL	128159	167805	131%
IN	190763	213962	112%
IT	661777	719438	109%
JP	2802730	3787058	135%
KR	1906273	3031637	159%
MX	524748	620385	118%
MY	121991	158129	130%
NL	144408	155950	108%
NO	370993	444431	120%
NZ	261084	329085	126%
PL	370207	492642	133%
PT	218054	249693	115%
RO	131598	152849	116%
RU	979629	1831707	187%
SE	406973	420885	103%
SG	277368	363149	131%
SU	1536615	1718307	112%
TW	1004135	1143685	114%
UA	164487	339271	206%
US	12270415	12630422	103%
WO	4119901	4756241	115%
ZA	511180	621550	122%

About the latter, number of records in TLS215 are more than doubled since for each category (now categories can be multiple as AD, AX ...) one line for relevant claim is created.

PATSTAT 2019a MySQL upload scripts

At this link are available my new scripts for MySQL to upload PATSTAT 2019a data.

New features in this ediction:


Person names in the original language (PERSON_NAME_ORIG_LG)
Field added in tables TLS206, 226, 906. This creates an inflation of records and lack of retrocompatibility with old personwise data.

“RELEVANT_CLAIM” attribute in the TLS215_CITN_CATEG table
A new attribute “RELEVANT_CLAIM” has been added to the TLS215_CITN_CATEG table. This attribute contains a single number referring to the claim to which the citation refers to.

webscraping: download of ANVUR list of journals in Python

Recently I had the task of creating a dataset of scientific journal classified by ANVUR (italian agency for research rating);
Unfortunately the lists are splitted by research area and available only in PDF at URL

http://www.anvur.it/attivita/classificazione-delle-riviste/classificazione-delle-riviste-ai-fini-dellabilitazione-scientifica-nazionale/elenchi-di-riviste-scientifiche-e-di-classe-a/

In order to make my life easier I created a Python 3 script that downloads all PDFs and via Tabula library, transforms PDF tables into CSVs.

I put the script below (note URL is hardcoded, for future uses change it)

Still CSVs need some work due to multiline titles.
To make life easie I made 2 xls files with A journals and all journals, for Areas 11 12 13 and 14.

XLS files can be downloaded here.




# python 35

# pdf downloader code extractor

from bs4 import BeautifulSoup
import requests
import time
import codecs
import PyPDF2
import os
from tabula import read_pdf
import pandas as pd

if __name__ == "__main__":


    istable = input('Pdf are tables?[N]') or 'N'
    dnl = input('download PDFs?[Y]') or 'Y'

    if dnl=="Y":
        archive_url = "http://www.anvur.it/attivita/classificazione-delle-riviste/classificazione-delle-riviste-ai-fini-dellabilitazione-scientifica-nazionale/elenchi-di-riviste-scientifiche-e-di-classe-a/"
        response = requests.get(archive_url)

        soup = BeautifulSoup(response.text, 'html.parser')

        pdf_links = [link['href'] for link in soup.find_all('a') if link['href'].endswith('pdf')]


        for link in pdf_links:

            if link[:4]!='http':
                link = archive_url + link

            '''iterate through all links in and download them one by one'''

            # obtain filename by splitting url and getting
            # last string
            file_name = link.split('/')[-1]

            print ("Downloading file:%s" % file_name)

            # create response object
            r = requests.get(link, stream=True)

            # download started
            with open(file_name, 'wb') as f:
                for chunk in r.iter_content(chunk_size=1024 * 1024):
                    if chunk:
                        f.write(chunk)

            print ("%s downloaded!\n" % file_name)

        print ("All file downloaded!")

    pdfDir = ""
    txtDir = ""

    if pdfDir == "": pdfDir = os.getcwd() + "\\"  # if no pdfDir passed in
    if txtDir == "": txtDir = os.getcwd() + "\\"  # if no txtDir passed in

    for pdf_to_read in os.listdir(pdfDir):  # iterate through pdfs in pdf directory
        fileExtension = pdf_to_read.split(".")[-1]  # -1 takes always last part
        if fileExtension == "pdf":
            pdfFilename = pdfDir + pdf_to_read
            textFilename = txtDir + pdf_to_read + ".txt"
            textFile = open(textFilename, "a")  # make text file

            if istable == 'N':

                pdf = PyPDF2.PdfFileReader(open(pdfFilename, "rb"))
                for page in pdf.pages:
                    textFile.write(page.extractText())  # write text to text file

            else:
                df= read_pdf(pdfFilename, pages="all")
                df.to_csv(textFilename)



            textFile.close()

PATSTAT data coverage

When doing advanced statistical analysis, it is important to understand the coverage and content of the data you are working with. PATSTAT Global contains data coming from all over the world. Quality, timeliness and completeness vary a great deal depending on patent office.


EPO compiled a Tableau  dashboard that maps the content and coverage of PATSTAT Global.


This chart shows the percentage of patent applications having a certain data element (e.g. CPC classification) by patent authority and application year in PATSTAT Global.

https://public.tableau.com/profile/patstat.support#!/vizhome/CoverageofPATSTAT2018AutumnEdition/CoveragePATSTATGlobal

PATSTAT autumn 2018 MySQL upload scripts

at this link is possible to download a batch of scripts for MySQL that will allow you to upload new PATSTAT edition autumn 2018.

This release has some improvements as:

* Table TLS201_APPLN and TLS211:  attribute granted changed from 0/1 to Y/N.

* Table TLS212_CITATION: Euro-PCT applications did not have the citations from the international search report linked to the respective application (and publication). These are the so called A0 publications. To avoid this, EPO simply duplicated the citations from the international search report, and linked them to the respective EP publications.

* Table TLS803_LEGAL_EVENT_CODE: has been redesigned to match WIPO ST.27.

PATSTAT projects on github

Refilling PATSTAT addresses

this project contains a docker container in Python and MySQL to refill persons where addresses is missin

https://github.com/cortext/patstat/tree/master/parsed%20addresses

Classify Legal Entities And Individuals From Patent Applicants

A batch of MySQL script to discriminate type of applicant

https://github.com/cortext/patstat/tree/master/applicants%20classification

Add official name of patent office


https://github.com/cortext/patstat/tree/master/nomenclatures/offices_classification

building descriptions for the International Patent Classification

An API embedded into a VM to get the full description of IPC codes


https://github.com/cortext/patstat/tree/master/nomenclatures/ipc_descriptions


PATSTAT loader

https://github.com/simonemainardi/load_patstat

psClean

Python library and associated code for preparing PATSTAT inventor-patent data for disambiguation with either the Torvik-Smallheiser or Open City Dedupe algorithms.

https://github.com/markhuberty/psClean

 

fuzzygeo

fuzzygeo provides a fuzzy geocoding routine for geocoding at the named entity (city or similar) level
https://github.com/markhuberty/fuzzygeo

psClassify
a simple supervised learning algorithm to classify PATSTAT records into two categories:

• person names
• not person names

https://github.com/mkln/psClassify
















 

Google dataset search

Recently Google launced a new service aiming to index local, public and national data repositories: Google Dataset Search.

Dataset Search lets you find datasets wherever they’re hosted, whether it’s a publisher's site, a digital library, or an author's personal web page.

Google also developed guidelines for dataset providers to describe their data in a way that search engines can better understand the content of their pages.

The approach is based on an open standard for describing this information (schema.org) and anybody who publishes data can describe their dataset this way.

The engine also links, where possible, the dataset to Google Scholar articles using them.


Full story @ link
https://www.blog.google/products/search/making-it-easier-discover-datasets/

How to build indicators from PATSTAT step by step

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

Patstat indicators step by step from Gianluca Tarasconi

MySQL upload scripts for EEE-PPAT 2018a

EEE-PPAT table is an extension of the PERSON TABLE (TLS206) produced by ECOOM (Catholic University of Leuven) and Eurostat. The extension concerns sector allocation and name harmonization of applicants.

2018a version contains 56611335 records, and it has improvements for standard names and sector, compared to PATSTAT ediction.


It can be required at no cost by contacting technoinfo@ecoom.be

MySql script I created for uploading the table can be downloaded here

Online regular expression validators

a quick note on two useful on line tools allowing test and validate regular expressions.

First one is

https://regex101.com

that allows tests in multiple dialects (php, javascript, python...) and gives a verbose but useful explanation of the steps.

Last but not least it allows to share links to tests prepared like this one:

https://regex101.com/r/9y9n85/20

also useful is:
https://regexper.com/


that comes with the comforting subtitle
You thought you only had two problems…
and allows crreating diagrams about how a given regexp works

example:

 





https://regexper.com/#Results%20%3D%20%28%5Cd%2B%29 

Linked Open EP data

at URL
https://www.epo.org/searching-for-patents/data/linked-open-data.html
is now available an linked open data version of EP patent data.

Linked Open EP data is a  data product provided by the EPO. It contains EP publications with their
bibliographic and  family information.
It also contains some basic information on non-EP patents, which are related to EP patents, e. g. because they are a priority of an EP document or they are in the same familyas an EP application.

The product also comes with a simple application programming interface (API), allowing you to consult reference data, explore that data and try out ideas on a small scale.
A SPARQL interface enables you to analyse the data.

Linked open EP data uses Uniform Resource Identifiers (URIs) to identify patent applications, publications and other resources present in patent data. This allows data in one dataset to be linked to data in another dataset. Given its URI, data about a resource can be retrieved in a variety of formats over the web. For occasional use there is a simple data browser, an API and a query interface. For heavier use, bulk data is available for download.

Each application has a unique identifier which looks like a URL and has this structure:

https://data.epo.org/linked-data/doc/application/cc/nnnnnnnn

where
cc stands for the Office code.
nnnnnnnn stands for the application number.

Applications disappearing across PATSTAT edictions

Interesting fact, especially if you have to run periodical reports: new edictions in PATSTAT not only add new application but a small number of existign applications disappear across edictions.

Below query counts patents disappearing. Results are listed in the table below for offices/appln_kind with more than 1000 disappearing applications

APPLN_AUTH	APPLN_KIND	EARLIEST_PUBLN_year	Count_APPLN_ID
'AU'	'A'	9999	1142
'AU'	'D'	1968	1067
'AU'	'D'	1969	1351
'AU'	'D'	1970	1458
'AU'	'D'	1971	1491
'AU'	'D'	1972	1389
'AU'	'D'	1973	7322
'AU'	'D'	1974	7474
'AU'	'D'	1975	4478
'AU'	'D'	1976	2554
'AU'	'D'	1977	4680
'AU'	'D'	1978	7522
'AU'	'D'	1979	11628
'AU'	'D'	1980	9765
'AU'	'D'	1981	7680
'AU'	'D'	1982	7652
'AU'	'D'	1983	7804
'AU'	'D'	1984	7995
'AU'	'D'	1985	8747
'AU'	'D'	1986	9701
'AU'	'D'	1987	9809
'AU'	'D'	1988	10245
'AU'	'D'	1989	6807
'AU'	'D'	1990	11410
'AU'	'D'	1991	10272
'AU'	'D'	1992	8128
'AU'	'D'	1993	5961
'AU'	'D'	1994	7735
'AU'	'D'	1995	9861
'AU'	'D'	1996	12173
'AU'	'D'	1997	11543
'AU'	'D'	1998	15028
'AU'	'D'	1999	15274
'AU'	'D'	2000	15162
'AU'	'D'	2001	11687
'AU'	'D'	2002	3623
'KR'	'A'	2005	1956
'KR'	'A'	2006	2954

Some offices show a systematic decrease of number of applications

office	app kind	earl pub date	n apps 2018a	n apps 2016b	Colonna1
'IT'	'A'	2010	9040	9257	-2%
'IT'	'A'	2011	9411	9681	-3%
'IT'	'A'	2012	8913	9152	-3%
'IT'	'A'	2013	8858	9180	-4%
'IT'	'A'	2014	8234	8494	-3%
'IT'	'A'	2015	3833	3865	-1%

SELECT
  a.APPLN_AUTH,
  a.APPLN_KIND,
  a.EARLIEST_PUBLN_year,
  Count(DISTINCT a.APPLN_ID) AS Count_APPLN_ID
FROM
  patstat2016b.tls201_appln a
  LEFT JOIN patstat.tls201_appln b ON b.APPLN_ID = a.APPLN_ID
WHERE
  b.APPLN_ID IS NULL
GROUP BY
  a.APPLN_AUTH,
  a.APPLN_KIND,
  a.EARLIEST_PUBLN_year,
  b.APPLN_ID