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