MySQL upload scripts for EEE-PPAT 2017a

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.

2017a version contains 54430027 records, but it has improvements for standard names and sector, compared to PATSTAT ediction.

Here below how sector allocation changes (note sector '' is for inventors/individual that have no allocation)

sector	patstat	eee-ppat
''	33318859	34172988
COMPANY	6189243	6350640
COMPANY GOV NON-PROFIT	58411	59098
COMPANY GOV NON-PROFIT UNIVERSITY	145	147
COMPANY HOSPITAL	884	876
COMPANY INDIVIDUAL		6
COMPANY UNIVERSITY	870	872
GOV NON-PROFIT	242180	249389
GOV NON-PROFIT HOSPITAL	15	17
GOV NON-PROFIT UNIVERSITY	8606	8752
HOSPITAL	12433	12650
INDIVIDUAL	11352954	11482216
UNIVERSITY	134947	143446
UNIVERSITY HOSPITAL	5	8
UNKNOWN	3110475	1948916

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

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

Using patstat in universities evaluation procedures

This work shows a methodology used to match PATSTAT inventor names to a full list of researchers working in Italian universities.
The goal is to have higher recall, leaving institutions/researchers to validate the data.
Focus will not be on results (evaluation still in progress) but on data processing, selection and match algorithm, highlighting some difficulties and relative workarounds.

Fixing 'see applicant' issue in patstat TLS207

Among the gift brought from EPO with oct. 2013 version, there is a change that is quite a worstening of data quality, since for a big number of applications (140.700: all of them at EP application authority) instead of the correct reference to the inventor person_id TLS207 lists person id 96 whose name (see applicant) do not let us presume it is a valid entry...

In order to fix it for the great majority of the records (140.680 of them) who list only one inventor (the famous 'see applicant') I wrote this lines of code that should fix the issue in mysql.
are left out about 20 records that have also inventors  other than 'see applicant' so a hand made correctin and check should be performed.



-- fixes for SEE APPLICANT (person id 96) on TLS207

use patstat;

-- back up old TLS207

create table tls207_pers_appln_back as select * from tls207_pers_appln;

-- temp table with

drop table if exists t01;

create table t01
select u.appln_id from tls207_pers_appln u inner join
(SELECT * FROM tls207_pers_appln t
WHERE t.`PERSON_ID`=96 and invt_seq_nr =1) as t on t.appln_id = u.appln_id
group by u.appln_id
having max(u.invt_seq_nr = 1);

alter table t01 add index id1(appln_id);

-- update : keep applicants order

update  tls207_pers_appln a inner join t01 t on t.appln_id = a.appln_id
set a.invt_seq_nr =   a.applt_seq_nr
where  a.invt_seq_nr = 0 and    a.applt_seq_nr > 0;

-- deletes old inventor 96 see also in updated applications

delete a.* from  tls207_pers_appln a inner join t01 t on t.appln_id = a.appln_id
where person_id = 96;

-- check: tabel should be empty

drop table if exists tchk01;

create table tchk01
Select t7.APPLN_ID, t7.INVT_SEQ_NR, Count(t7.PERSON_ID) as c
From   tls207_pers_appln t7
where t7.INVT_SEQ_NR > 0
Group By t7.APPLN_ID, t7.INVT_SEQ_NR
Having   Count(t7.PERSON_ID) > 1


-- exit drops temp tables

drop table t01;

Mysql script for importing OECD regpat

OECD regpat is a set plug and play tables that set on Patstat adding a batch of useful info as NUTS code, IPC, counts etc (see for more info http://www.oecd.org/sti/inno/oecdpatentdatabases.htm)

I put here a small script for importing in mysql 2 of the gived tables: ep_app_reg and ep_inv_reg giving especially regional NUTS3 code for EP inventors and applicants.

just be careful to replace YOURDIRECTORYHERE with the folder where you put the files from regpat.



-- Import REGPAT Mysql script
-- List of EPO applicants

drop table if exists EP_APP_REG;
drop table if exists EP_INV_REG;

create table  EP_APP_REG
(App_nbr varchar(13),
Appln_id varchar(11),
Pub_nbr varchar(11), -- EPO patent publication number
Person_id varchar(11), -- Surrogate key - person identifier in PATSTAT, October 2013
App_name varchar(300), -- Applicantfs name
Address varchar(500), -- Address
Reg_code varchar(10), -- NUTS3/TL3 region code
Ctry_code varchar(2), -- ISO 2 country code
Reg_share2 varchar(7), -- Multiple allocation to a region - Share . 1
App_share3 varchar(7)) -- Applicantfs share . 1
ENGINE=MyISAM
CHARACTER SET utf8 COLLATE utf8_general_ci;

create table  EP_INV_REG
(App_nbr varchar(13),
Appln_id varchar(11),
Pub_nbr varchar(11), -- EPO patent publication number
Person_id varchar(11), -- Surrogate key - person identifier in PATSTAT, October 2013
Inv_name varchar(300), -- Applicantfs name
Address varchar(500), -- Address
Reg_code varchar(10), -- NUTS3/TL3 region code
Ctry_code varchar(2), -- ISO 2 country code
Reg_share2 varchar(7), -- Multiple allocation to a region - Share . 1
Inv_share3 varchar(7)) -- Applicantfs share . 1
ENGINE=MyISAM
CHARACTER SET utf8 COLLATE utf8_general_ci;

LOAD DATA INFILE 'YOURDIRECTORYHERE/201401_EPO_App_reg.txt' INTO TABLE EP_APP_REG
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;

LOAD DATA INFILE 'patstat/raw/201401_EPO_Inv_reg.txt' INTO TABLE EP_INV_REG
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES -- fixes format data: default import = text

ALTER TABLE ep_app_reg MODIFY COLUMN Appln_id INT(11) UNSIGNED DEFAULT NULL,
 MODIFY COLUMN Person_id INT(11) UNSIGNED DEFAULT NULL,
 MODIFY COLUMN Reg_share2 DOUBLE DEFAULT NULL,
 MODIFY COLUMN App_share3 DOUBLE DEFAULT NULL;

ALTER TABLE ep_inv_reg MODIFY COLUMN Appln_id INT(11) UNSIGNED DEFAULT NULL,
 MODIFY COLUMN Person_id INT(11) UNSIGNED DEFAULT NULL,
 MODIFY COLUMN Reg_share2 DOUBLE DEFAULT NULL,
 MODIFY COLUMN Inv_share3 DOUBLE DEFAULT NULL;

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;

MySQL upload scripts for EEE-PPAT october 2013

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



October 2013 ediction contains 44730405 records, but it has standardized names and sectors for 15.291.352 legal person (not for individuals).

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

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

PATSTAT- TM - ORBIS match from EPO-OHIM

What follows is a personal sinthesys including a lot of extracts from: 

Intellectual property rights intensive industries: contribution to economic performance and employment in the European Union
http://www.epo.org/service-support/publications/studies/ip-intensive-industries.html

especially aimed on chapter 9 where match among patents and TM applicants and Orbis companies has been performed.

Harmonization and matching:

Convert name to the uppercase

Clean legal form information

A dictionary was created, containing 480 regular

expressions (regex) allowing for identification and removal of legal forms typical in each Member State of the European Union. For some countries (BE, DE, PL), a second step of legal form cleaning was added. In the case of Belgium, the purpose was mainly to look for cases where the legal form was indicated in both French and Dutch. For Germany and Poland, the second cleaning loop was designed to deal with composite legal forms, such as GMBH CO KG

Convert special characters using NFKD normalizer

By default, IP registers can record applicant names using the national characters of the country of origin. Nevertheless, sometimes applicants or their legal representatives file new applications with the name already converted into its Latin equivalent, without any specific national characters. This problem was dealt with by applying the Normalization Form Compatibility Decomposition (NFKD) Unicode normalisation transformation procedure implemented in Java. This allowed for automatic conversion of all the names into the normalised forms.

Clean other characters,remove double spaces etc.

In a further pre-processing step all characters other than a-zA-Z0-9&@$+ were replaced with a space, and periods were removed. Leading and trailing whitespaces were also removed, and

multiple whitespaces were reduced to one space.

Clean nondistinctive, weak words

As a first step, each country was assigned a code specific to that country/language, and non distinctive words were removed from the normalised names. The list of non-distinctive words was based on a calculation of the presence of words within the firms’ names and a thorough, labour intensive

analysis of each data set. This part of the procedure was not wholly automatic, as not all

the relatively frequent words were removed from the normalised name field through the automated procedure. By the same token, some words that are relatively less frequent than others were removed from the normalised names because, after analysis of each dataset, it turned out that they were not distinctive.

Further refinements have been: separate match of natural and legal persons;

Trading as (language sensitive) denomination has been separated from original name

Names obtained are matched.

After the initial matching phase, the one-to-one matches (one EPO/OHIM record matched with only one ORBIS record) were filtered out, and one-to-many matches (where one EPO/OHIM record matched several ORBIS records) were selected for further processing.

At this stage, additional information (other than the firm name) was used.

The ORBIS dataset contains a field called DUO (domestic ultimate owner). As a first step, all the companies from the ORBIS dataset were grouped by their normalised name and a check was carried out to establish how many unique DUO numbers corresponded to each group. If there was only one DUO number associated with several ORBIS firms with the same normalised name, then the record associated with that company was taken as a potential match.

ORBIS Domestic Ultimate Owner CHECK

Before matching those records, the completeness of the DUO company record was compared with that of the other companies in the group, in terms of turnover and employment reported. This was necessary because no information was available on whether the DUO company was consolidating accounts of its subsidiaries. Therefore, the EPO/OHIM record was matched to only one relevant ORBIS record (DUO or subsidiary), namely that with the highest turnover and employment figures within the group.

ROOT BVD ID CHECK

Now, groups of ORBIS records with the same normalised name and the same Bureau van Dijk(BvD) id root were identified (Sometimes ORBIS branches or subsidiaries have the same number as the parent company, with additional digits separated from the root number with a hyphen.)

This hyphen and all digits following the hyphen were stripped off to check whether all the ORBIS companies with the same normalised name had the same root BvD id number. If so, the EPO/OHIM record was linked with the company whose BvD id number was the root number for all ORBIS companies with the same normalised name.

LEGAL FORM CHECK

Subsequently, the algorithm checked whether among the ORBIS companies with the same normalised name there was only one company with the same legal form as at least one company in the EPO/OHIM database.

ZIP CODE CHECK

In a final attempt to find a unique match, the postal codes in the EPO-OHIM record were compared with those in the various ORBIS records matched to it. If only one ORBIS record matched the postal code in the EPO‑OHIM record, it was added to the matched dataset.

After all checks above, the matched records which still had one-to-many relationships following the disambiguation process were disregarded.

MANUAL SEARCH

For the manual checking process, applicant information from sources other than ORBIS was used, such as national business registers or company websites, in order to find the reason for the non‑match.

In some cases, for example, it could be established that the company had recently changed its name. In such cases, this new piece of information was used to query the ORBIS database again.

Thus, the normalised name in ORBIS sometimes did not correspond to the normalised name in the OHIM/EPO database.

CONCORDANCE TABLES

three tables were produced:

ORBIS-EPO concordance:

person_id number from the tls206_person - BvD idnumber from the ORBIS dataset.

ORBIS-OHIM concordance

owner_code from the dim_owner table of OHIM’s datawarehouse and the BvD id number from the ORBIS dataset.

EPO-OHIM concordance

person_id number from the tls206_person table of PATSTAT and the owner_code from the dim_owner table of OHIM’s datawarehouse.

Redistribution from head offices

One problem identified during the initial calculations was the presence of some general, non-specific industry codes, namely 7010

Activities of head offices, 6420 Activities of holding companies and 8299 Other business support service activities n.e.c.

This practice could potentially have distorted the industry-intensity analysis if some industries were more prone than others to leave maintenance of their trade mark / patent portfolio to the holding company/head office, as those industries would then be underrepresented in the general classification.

Redistribution of patents from head offices

In terms of absolute patent intensity, those industries codes 7010, 6420 and 8299 were ranked second, third and 27th, respectively. This phenomenon reflects the common business practice of concentrating patent portfolios at head offices, which also handle all the relevant filing and registering procedures.

codes 7010, 6420 and 8299 were analysed in ORBIS by:

assigning the match to the subsidiaries with the same name in case the match was to a DUO

assigning the match to sisters subsidiaries (realted to same DUO) in case the match is not a DUO

Redistribution of TM from head offices

Same problem as above for codes 7010, 6420 and 8299 (ranking 1st 2nd and 3rd). The problem was dealt with in exactly the same manner

as described in the previous section.

NACE codes at different levels of aggregation

In some cases, ORBIS assigns to a firm the NACE code at a higher level of aggregation (3-digit group or 2-digit division) when in the NACE classification those codes could be disaggregated into a lower level of analysis (class). For computational reasons, ORBIS adds one or two zeros to such group or division codes in order to create 4-digit classes in all records. These classes are referred to as synthetic classes.

It was decided to deal with this problem by redistributing the patents associated with synthetic classes among the classes within the division or group, as applicable.

MySql upload script for EEE-PPAT october 2012

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

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

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

EDIT: 2015b data have one column less: here the scripts for uploading them

EEE-PPAT: Released version oct 2011

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

It can be required at no cost by contacting technoinfo@ecoom.be; some documentation @ this link 


It contains the following columns with original PATSTAT person_id:
        - PERSON_ID
        - HRM_LEVEL1: harmonized name level1
        - SECTOR : Sector of assignee name 


The file is coded in UTF-8, tab-delimited.
The definition of table is as follow:
        - PERSON_ID                       number(9)
        - HRM_LEVEL1                   char  (400)
        - SECTOR                             char  (50)


The file, tab delimited, contains 12488647 records.


The 'compression rate' is good: out of 10.324.068 distinct applicant names contained in TLS206, EE-PPAT reduces them into 8.227.328, and sector allocation is distributed as follows:

COMPANY	2173055
COMPANY GOV HOSPITAL	1
COMPANY GOV NON-PROFIT	35307
COMPANY GOV UNIVERSITY	176
COMPANY HOSPITAL	1601
COMPANY UNIVERSITY	1544
GOV NON-PR0FIT	4
GOV NON-PROFIT	105750
GOV NON-PROFIT UNIVERSITY	669
HOSPITAL	5028
INDIVIDUAL	4774071
UNIVERSITY	45506
UNKNOWN	1252219

[Lines below are cancelled since data have been corrected with updated the data on FTP on 2012 Jan. 10 CET 13:51.]


(some look like small mistakes like GOV NON-PR0FIT)



While loading the data you can have an error with person_id  4264883, 9883343, 8758108 that have sector null since the text COMPANY was 'taken' in the name harmonized name that finished with a slash, making problem to the recognition of tab field delimiter in the three records.


@ this link you can download a script for loading the table EEE-PPAT into mysql; it also has a patch for the 3 wrong records. 

[do not use the patch for 2012 Jan. 10 CET 13:51. data]

Error in TLS206 ASCII version sept. 2011

By comparing the 2 versions of TLS206 I realized there is an error that makes TLS206 ASCII impossible to be used, in sept. 2011 version.

Starting from person_id 1340952 there is a misalignment among the 2 files since the associated names are:

 'ARANAGA TOMOYUKI' for TLS206

 while
'ARANAGA YASUNORI' is in TLS206 ASCII

By checking on TLS207 if ind that the corrisponding appln_id  36165645 has publication number JP        11158733
and in espacenet I see the TLS206 inventor is the right one.

After a while (260K records) error disappears with this record:

1544759, 'ASAHINA', 'ASAHINA AKI', '', ''

but the bad thing is that it happens alsewhere.
F.I.

20000000, 'LESLIE * SMITH' <> 'LESLIE * STEELE', '', ''
25000000, 'NUECHTER,PATRICK' <> 'Nuechter', 'Peter', ''
30000001, 'Schaevitz; Lester P.' <>  'SCHAEVITZ, SAM', '', ''

So the bad news are that, since person_id is build by appending in alfabetic order the names, there are ranges of person_id in TLS206 ASCII where the names accociated to person_id is wrong.

EPO is aware of the problem and is  working to a fix.