Thursday, June 14, 2012

EU27 patent authorities inventors country analisys (2/3)

In my last post I calculated data coverage for EU27 application authorities.


Eventually we run two analisys:
First aims to get overall figures and percent of application (calculated in fractional way) that have inventors with country codes (Y), no country codes (N) or no inventors (V).
Note we left in first batch D2 application kind cause they will be drilled down in second batch.

Q5:  totals by appln authorities in EU27

Select
  a.EU27sort,
  a.APPLN_AUTH,
  a.CYDATA,
  Sum(a.weight) As total
From
Nationalcounts as a
Group By
  a.EU27sort, a.APPLN_AUTH, a.CYDATA;

Q5b:  totals by appln authorities in EU27

Select
  a.EU27sort,
  a.APPLN_AUTH,
  a.CYDATA,
  Sum(a.weight) As total
From
Nationalcounts as a
Group By
  a.EU27sort, a.APPLN_AUTH, a.CYDATA
where appln_kind <> “D2”;

 
We may run also a second analysis to understand if missing information are biased among different application types.

Q6: totals by appln authority, appln kind in EU27

Select
  a.EU27sort,
  a.APPLN_AUTH,
  a.APPLN_KIND,
  a.CYDATA,
  Sum(a.weight) As total
From
Nationalcounts as a
Group By
  a.EU27sort, a.APPLN_AUTH, a.CYDATA, a.appln_kind;


Here follow results obtained turning query output into pivot tables


 'N'
 'V'
 'Y'


#
%
#
%
#
%
total applications
 'AT'
3991.9386
1.51%
19675
7.47%
239881.6556
91.02%
263548.5942
 'BE'
5400.9078
1.90%
275275
96.77%
3785.6805
1.33%
284461.5883
 'BG'
6.1665
0.08%
602
7.93%
6979.4896
91.98%
7587.6561
 'CY'
21.6039
4.41%
36
7.35%
432.3883
88.24%
489.9922
 'CZ'
29.262
0.10%
711
2.32%
29924.4992
97.59%
30664.7612
 'DE'
7742.6243
0.37%
1092082
52.86%
966333.3404
46.77%
2066157.965
 'DK'
8303.1047
6.97%
47024
39.48%
63787.1083
53.55%
119114.213
 'EE'
57.5436
1.87%
114
3.70%
2906.9595
94.43%
3078.5031
 'ES'
9363.1948
5.13%
8642
4.73%
164646.4488
90.14%
182651.6436
 'FI'
268.0875
0.31%
25157
29.12%
60971.4663
70.57%
86396.5538
 'FR'
128760.1764
42.08%
97507
31.87%
79723.3037
26.05%
305990.4801
 'GB'
2871.6394
0.70%
235878
57.21%
173546.1015
42.09%
412295.7409
 'GR'
4227.6262
40.17%
587
5.58%
5709.0193
54.25%
10523.6455
 'HU'
21.0954
0.08%
12032
47.52%
13267.1454
52.40%
25320.2408
 'IE'
71.2504
0.86%
2245
27.04%
5986.7069
72.10%
8302.9573
 'IT'
109618.3051
64.40%
45064
26.47%
15533.4011
9.13%
170215.7062
 'LT'
5.3335
0.45%
7
0.59%
1164.4153
98.95%
1176.7488
 'LU'
21.0664
0.18%
11023
95.18%
536.9265
4.64%
11580.9929
 'LV'
2.75
0.16%
43
2.45%
1709.2437
97.39%
1754.9937
 'MT'

0.00%
18
100.00%

0.00%
18
 'NL'
328.8787
0.15%
188765
83.65%
36570.1221
16.21%
225664.0008
 'PL'
567.0249
1.04%
3625
6.67%
50193.7565
92.29%
54385.7814
 'PT'
11.411
0.04%
943
3.28%
27803.6665
96.68%
28758.0775
 'RO'
4.7
0.09%
1160
23.02%
3875.0973
76.89%
5039.7973
 'SE'
32285.2309
4.27%
695851
92.12%
27206.9115
3.60%
755343.1424
 'SI'
12.851
0.10%
189
1.51%
12299.9429
98.39%
12501.7939
 'SK'
19.5667
0.26%
330
4.37%
7208.7045
95.38%
7558.2712
Total
314013.3397
6.18%
2764585
54.41%
2001983.501
39.40%
5080581.841

 We notice about 55% (15 countries) overcome 70% of coverage, but the problem comes with offices who have big figures(FI DE and GB) where coverage is below 50%, decreasing overall % to nearly 40%
  
We run once more same data analysis removing application kind D2 (PATSTAT dummy kind code for artificial applications) to understand if some significant change take place.

 'N'
 'V'
 'Y'


#
%
#
%
#
%
total applications
 'AT'
3991.9386
1.52%
18911
7.20%
239881.6556
91.28%
262784.5942
 'BE'
5400.9078
1.91%
273210
96.75%
3785.6805
1.34%
282396.5883
 'BG'
6.1665
0.08%
497
6.64%
6979.4896
93.28%
7482.6561
 'CY'
21.6039
4.45%
32
6.58%
432.3883
88.97%
485.9922
 'CZ'
29.262
0.10%
160
0.53%
29924.4992
99.37%
30113.7612
 'DE'
7742.6243
0.40%
985045
50.28%
966333.3404
49.32%
1959120.965
 'DK'
8303.1047
7.30%
41668
36.63%
63787.1083
56.07%
113758.213
 'EE'
57.5436
1.87%
106
3.45%
2906.9595
94.67%
3070.5031
 'ES'
9363.1948
5.29%
2994
1.69%
164646.4488
93.02%
177003.6436
 'FI'
268.0875
0.32%
22996
27.30%
60971.4663
72.38%
84235.5538
 'FR'
128760.1764
43.47%
87697
29.61%
79723.3037
26.92%
296180.4801
 'GB'
2871.6394
0.74%
212899
54.69%
173546.1015
44.58%
389316.7409
 'GR'
4227.6262
40.84%
415
4.01%
5709.0193
55.15%
10351.6455
 'HU'
21.0954
0.09%
10479
44.09%
13267.1454
55.82%
23767.2408
 'IE'
71.2504
0.88%
2015
24.96%
5986.7069
74.16%
8072.9573
 'IT'
109618.3051
80.17%
11578
8.47%
15533.4011
11.36%
136729.7062
 'LT'
5.3335
0.45%
6
0.51%
1164.4153
99.04%
1175.7488
 'LU'
21.0664
0.18%
10976
95.16%
536.9265
4.66%
11533.9929
 'LV'
2.75
0.16%
43
2.45%
1709.2437
97.39%
1754.9937
 'MT'

0.00%
18
100.00%

0.00%
18
 'NL'
328.8787
0.15%
185662
83.42%
36570.1221
16.43%
222561.0008
 'PL'
567.0249
1.08%
1918
3.64%
50193.7565
95.28%
52678.7814
 'PT'
11.411
0.04%
926
3.22%
27803.6665
96.74%
28741.0775
 'RO'
4.7
0.12%
202
4.95%
3875.0973
94.94%
4081.7973
 'SE'
32285.2309
4.36%
680737
91.96%
27206.9115
3.68%
740229.1424
 'SI'
12.851
0.10%
177
1.42%
12299.9429
98.48%
12489.7939
 'SK'
19.5667
0.26%
302
4.01%
7208.7045
95.73%
7530.2712
Total
314013.3397
6.45%
2551669
52.42%
2001983.501
41.13%
4867665.841

Figures remain about the same: 15 countries overcome 70% of coverage, but overall % rises of only 2% to nearly 42%

Next post will finish this issues (coming soon)

No comments:

Post a Comment