Transformation between IPC and NACE

Here you can post your opinions, ask questions and share experiences on the PATSTAT product line. Please always indicate the PATSTAT edition (e.g. 2015 Autumn Edition) and the database (e.g. PATSTAT Online, MySQL, MS SQL Server, ...) you are using.
Post Reply

torben
Posts: 13
Joined: Thu Nov 12, 2015 9:59 am

Transformation between IPC and NACE

Post by torben » Thu Nov 12, 2015 10:09 am

Hello,
I’m a student at the universtity of Bremen. Im searching for a query which identifies patents with at least 3 different NACE classes generated out of the IPC-Classes.
With that approach I want to identify cross-industry innovation.

I found a paper about the concordance between IPC and NACE provided by Eurostat in co-operation with KU Leven/ Belgium. https://circabc.europa.eu/sd/a/d1475596 ... 2_2014.pdf

I need a query with following boundary conditions:
It’s necessasry for each patent to count the different IPC classes. If the number of different IPC classes is equal or greater than 3 than it has to convert the IPC classes into NACE classes (based on method of the paper from Eurostat).
After that it has to check if there are at least 3 different NACE classes.

So I'm new in programming a SQL-code and my questions are:
- Is it possible to count IPC classes from one Patent?
- Is it possible check if there are at least 3 different NACE clases for one patent?


nico.rasters
Posts: 140
Joined: Wed Jul 08, 2009 5:51 pm
Contact:

Re: Transformation between IPC and NACE

Post by nico.rasters » Fri Nov 20, 2015 2:37 pm

Do you have access to a local installation of PATSTAT or are you working with PATSTAT Online?
Are you looking at the full IPC class, or only at for example the subclass?
________________________________________
Nico Doranov
Data Manager

Daigu Academic Services & Data Stewardship
http://www.daigu.nl/


torben
Posts: 13
Joined: Thu Nov 12, 2015 9:59 am

Re: Transformation between IPC and NACE

Post by torben » Sun Nov 22, 2015 8:41 am

Hello nico.rasters,
thanks for your reply. I have an access to a local installation of PATSTAT. And I'm using Microsoft Server Management Studio.

I'm looking for the IPC4-digit subclass, for example: B23F. I identified the table "ipc_class_symbol" to get the relevant Information. But the problem is, that in the table are more informations as I wanted.

I think I need three functions for my approach to identify cross-industry innovation.
- first fuction has to check if there are equal or more than three different IPC classes in one Patent
- second fuction has to convert IPC classes into NACE categories
- third function has to check if there are equal or more than three different NACE categories (It could be possible that a patent has got 3 different IPC classes but less than 3 NACE categories)


nico.rasters
Posts: 140
Joined: Wed Jul 08, 2009 5:51 pm
Contact:

Re: Transformation between IPC and NACE

Post by nico.rasters » Sun Nov 22, 2015 10:47 am

Create three new tables:
  • appln_ipc4
  • ipc4_nace
  • appln_nace
I'm running MySQL and I would use the MyISAM engine instead of InnoDB. Don't know what options you have on Microsoft Server.

INSERT INTO `appln_ipc4` (`appln_id`, `ipc4`)
SELECT DISTINCT `appln_id`, LEFT(`ipc_class_symbol`,4)
FROM `tls209_appln_ipc`
WHERE `ipc_class_level`="A" AND `ipc_version`="2006-01-01";

Put an INDEX index on `ipc4`.

This is based on PATSTAT October 2014. If you have a different version then you may need to adjust the query a bit. I've added `ipc_class_level` and `ipc_version` because your IPC-NACE concordance table will be based on a specific IPC version.

Import your IPC-NACE concordance table into your `ipc4_nace` table.

INSERT INTO `appln_nace` (`appln_id`, `nace`)
SELECT DISTINCT `ipc4_nace`.`appln_id`, `ipc4_nace`.`nace`
FROM `appln_ipc4` INNER JOIN `ipc4_nace`
ON `appln_ipc4`.`ipc4` = `ipc4_nace`.`ipc4`;

If you are using weights in your concordance then you will need to use SUM(`weight`) as a third variable and leave out DISTINCT.

Put a PRIMARY index on `appln_id`, `nace` in `appln_nace`.

SELECT `appln_id`, COUNT(`nace`)
FROM `appln_nace`
GROUP BY `appln_id`
HAVING COUNT(`nace`)>=3;

Personal comment: I've mentioned it before on this forum... I think an IPC-NACE concordance is a strictly academic exercise. Maybe it made sense once, but these days we have emerging and converging technologies. It's better to find the applicants in some company database and get their industry codes.

The story above does not take patent families into account. Probably it should.

It also does not take corporate ownership into account. Suppose a biopharma company owns an IT company. Would this afffect cross-industry innovation? I'll leave the answer up to you ;)
Alliances and joint ventures are also disregarded. Not to mention inventor mobility. Or that companies may change their core business.

These are not things that have to be solved, but I firmly believe in footnotes.
________________________________________
Nico Doranov
Data Manager

Daigu Academic Services & Data Stewardship
http://www.daigu.nl/


Geert Boedt
Posts: 178
Joined: Tue Oct 19, 2004 10:36 am
Location: Vienna

Re: Transformation between IPC and NACE

Post by Geert Boedt » Tue Nov 24, 2015 11:54 am

Hello Torben,
interesting approach, first time I see the "number of NACE classes" being used as an indicator.
I think there is a fairly easy solution.
Assuming you have a 2015 PATSTAT version , there is a pre-calculated IPC/NACE concordance table available at APPLICATION level. (tls229_appln_nace2). This table took into account the IPC/NACE concordance table and a weighing factor based on the fact that there can be multiple IPC's for 1 application.

Keeping in mind that 1 IPC code (sometimes in combination with an exclusion of another IPC code) refers to exactly 1 NACE code, you don't need to count the IPC codes. Simply count the NACE codes. As they have been weighed, you even know the "weight" of the specific code for that specific application. I think the easiest way to limit the sample to applications that have >2 codes is to join it with at table that only contains those applications. But as Nico mentioned, you can store those in another temporary table which is better if this part of a larger data aggregation exercise.

Query (limited to 1000 aplications):

SELECT TOP 1000 tls201_appln.appln_id,appln_auth,appln_nr,appln_kind,appln_filing_date,
granted, nb_citing_docdb_fam, nb_applicants,nb_inventors, weight,
tls229_appln_nace2.nace2_code, nace2_descr
FROM tls201_appln
join tls229_appln_nace2 on tls201_appln.appln_id = tls229_appln_nace2.appln_id
join (select appln_id , count(distinct(nace2_code)) count_nace from tls229_appln_nace2
group by appln_id having count(distinct(nace2_code)) > 2) more_then_2
on tls201_appln.appln_id = more_then_2.appln_id
join tls902_ipc_nace2 on tls229_appln_nace2.nace2_code = tls902_ipc_nace2.nace2_code
-- joined with tls902, just for readability :)
group by tls201_appln.appln_id,appln_auth,appln_nr,appln_kind,appln_filing_date,granted,
nb_citing_docdb_fam,nb_applicants,nb_inventors,weight,tls229_appln_nace2.nace2_code,
nace2_descr
order by tls201_appln.appln_id


The result table will show 1 row for each NACE code per application. If you don't need the detail of the NACE 2 codes, you can ofcourse exclude it from your select and group by statement, and as such reduce the size of the output table.

Geert Boedt
PATSTAT user support
Best regards,

Geert Boedt
PATSTAT support
Business Use of Patent Information
EPO Vienna


nico.rasters
Posts: 140
Joined: Wed Jul 08, 2009 5:51 pm
Contact:

Re: Transformation between IPC and NACE

Post by nico.rasters » Tue Nov 24, 2015 7:12 pm

You can tell I'm several versions behind ;) Thank you Geert for your answer.
The only worry I had there was that the concordance might be different from the one Torben wanted to use, but as I can read in the data catalog regarding TLS902_IPC_NACE2: The data on which this table is based is provided by EUROSTAT in co-operation with KU Leuven / Belgium. The data and the methodology to create them is described in ... same link as mentioned by Torben.

So Torben, if you have the 2015 Autumn Edition you can go for Geert's solution. Otherwise you'll have to use mine.
________________________________________
Nico Doranov
Data Manager

Daigu Academic Services & Data Stewardship
http://www.daigu.nl/


Post Reply