Territorial Economic Data viewer: A data integration and visualization tool

Marques Santos, Anabela et al. Working Paper Territorial Economic Data viewer: A data integration and visualization tool JRC Working Papers on Territorial Modelling and Analysis, No. 04/2023 Provided in Cooperation with: Joint Research Centre (JRC), European Commission Suggested Citation: Marques Santos, Anabela et al. (2023) : Territorial Economic Data viewer: A data integration and visualization tool, JRC Working Papers on Territorial Modelling and Analysis, No. 04/2023, European Commission, Joint Research Centre (JRC), Seville This Version is available at: https://hdl.handle.net/10419/283089 Standard-Nutzungsbedingungen: Die Dokumente auf EconStor dürfen zu eigenen wissenschaftlichen Zwecken und zum Privatgebrauch gespeichert und kopiert werden. Sie dürfen die Dokumente nicht für öffentliche oder kommerzielle Zwecke vervielfältigen, öffentlich ausstellen, öffentlich zugänglich machen, vertreiben oder anderweitig nutzen. Sofern die Verfasser die Dokumente unter Open-Content-Lizenzen (insbesondere CC-Lizenzen) zur Verfügung gestellt haben sollten, gelten abweichend von diesen Nutzungsbedingungen die in der dort genannten Lizenz gewährten Nutzungsrechte. Terms of use: Documents in EconStor may be saved and copied for your personal and scholarly purposes. You are not to copy documents for public or commercial purposes, to exhibit the documents publicly, to make them publicly available on the internet, or to distribute or otherwise use the documents in public. If the documents have been made available under an Open Content Licence (especially Creative Commons Licences), you may exercise further usage rights as specified in the indicated licence. https://creativecommons.org/licenses/by/4.0/ Territorial Economic Data viewer: A data integration and visualization tool JRC Working Papers on Territorial Modelling and Analysis No 04/2023 Authors: Marques Santos, A. Conte, A. Ojala, T. Meyer, N. Kostarakos, I. Santoleri, P. Shevtsova, Y. De Quinto Notario, A. Molica, F. Lalanne, M. Joint Research Centre 2023 JRC TECHNICAL REPORT rvice. It aims to provide evidence-based scientific support to the European policymaking process. The contents of this publication do not necessarily reflect the position or opinion of the European Commission. Neither the European Commission nor any person acting on behalf of the Commission is responsible for the use that might be made of this publication. For information on the methodology and quality underlying the data used in this publication for which the source is neither Eurostat nor other Commission services, users should contact the referenced source. The designations employed and the presentation of material on the maps do not imply the expression of any opinion whatsoever on the part of the European Union concerning the legal status of any country, territory, city or area or of its authorities, or concerning the delimitation of its frontiers or boundaries. Contact information Name: Anabela M. Santos Address: Edificio Expo, C/Inca Garcilaso 3, 41092 Sevilla (Spain) Email: [email protected] Tel.: +34 95 448 71 61 EU Science Hub https://joint-research-centre.ec.europa.eu JRC133404 Seville: European Commission, 2023 © European Union, 2023 The reuse policy of the European Commission documents is implemented by the Commission Decision 2011/833/EU of 12 December 2011 on the reuse of Commission documents (OJ L 330, 14.12.2011, p. 39). Unless otherwise noted, the reuse of this document is authorised under the Creative Commons Attribution 4.0 International (CC BY 4.0) licence (https://creativecommons.org/licenses/by/4.0/). This means that reuse is allowed provided appropriate credit is given and any changes are indicated. How to cite this report: Marques Santos, A.; Conte, A.; Ojala, T.; Meyer, N.; Kostarakos, I.; Santoleri, P.; Shevtsova, Y.; de Quinto Notario, A.; Molica, F. and Lalanne, M. (2023). Territorial Economic Data viewer: A data integration and visualization tool. JRC Working Papers on Territorial Modelling and Analysis No. 04/2023, European Commission, Seville, JRC133404. The JRC Working Papers on Territorial Modelling and Analysis are published under the supervision of Simone Salotti, Andrea Conte, and Anabela M. Santos of JRC Seville, European Commission. This series mainly addresses the economic analysis related to the regional and territorial policies carried out in the European Union. The Working Papers of the series are mainly targeted to policy analysts and to the academic community and are to be considered as early-stage scientific papers containing relevant policy implications. They are meant to communicate to a broad audience preliminary research findings and to generate a debate and attract feedback for further improvements. 1 Territorial Economic Data viewer: A data integration and visualization tool Version: 15/05/2023 Anabela M. Santos* |corresponding author ([email protected]) Andrea Conte* (Andre[email protected]opa.eu) Tauno Ojala* ([email protected]) Niels Meyer* (Niels.MEY[email protected]ropa.eu) Ilias Kostarakos* ([email protected]) Pietro Santoleri* ([email protected]) Yevgeniya Shevtsova* ([email protected]) Alicia de Quinto Notario* (Alicia.DE-QUINTO-NOT[email protected]ropa.eu) Francesco Molica* ([email protected]) Marie Lalanne* ([email protected]) * Joint Research Centre, European Commission, Seville, Spain JRC TECHNICAL REPORT JRC Working Papers on Territorial Modelling and Analysis No 04/2023 INDEX Executive Summary ..................................................................................................................................................... 3 1. Context ...................................................................................................................................................................... 6 2. The Territorial Economic Data viewer (TEDv): An overview ........................................................................ 7 3. Data source behind TEDv statistics ..................................................................................................................... 8 4. Methodological approach behind the TEDv ...................................................................................................... 9 4.1. Estimation of territorial statistics ....................................................................................................................... 9 4.2. Territorial benchmarking: identifying similar regions ................................................................................... 11 4.3. Data visualisation techniques ............................................................................................................................ 13 5. Description of the applicability of TEDv .......................................................................................................... 13 5.1. Regional Dashboard ........................................................................................................................................... 13 5.2. Sectorial Dashboard ........................................................................................................................................... 15 5.3. Comparison Dashboard .................................................................................................................................... 16 5.4. Regional info-sheet ............................................................................................................................................. 19 2 6. Policy relevance of TEDv .................................................................................................................................... 21 References ................................................................................................................................................................... 23 Appendix ..................................................................................................................................................................... 24 Appendix A. Horizon 2020 ...................................................................................................................................... 24 Appendix B. An illustration of the territorial benchmarking tool included in the comparison dashboard 25 Definitions .................................................................................................................................................................. 27 Glossary ....................................................................................................................................................................... 28 3 Executive Summary The Territorial Economic Data viewer (TEDv) is the first available tool that combines statistical territorial information from various European Union’s Research and Innovation (R&I) funding programs within a single and coherent framework. It compiles information from multiple data sources (Figure 1) and combines beneficiary-level data (micro-level) with regionalor country-level data (macro-level) to produce comprehensive territorial statistics. Figure 1. Data sources behind the Territorial Economic Data viewer (TEDv) Source: Own elaboration. The TEDv was developed to assist policymakers in monitoring the use of various Research and Innovation (R&I) funding programs. This task can be quite challenging as information about the final beneficiaries and territorial allocation of different funds is not available in a single data repository. Government bodies, managing authorities and stakeholders have to navigate through several data sources/webpages, sometimes with information expressed in different formats or taxonomies. The TEDv aims to address these challenges, by integrating data from multiple sources and presenting territorial statistics in a user-friendly format. The TEDv includes territorial statistics (at the country-, NUTS 1or NUTS 2-level) from three different R&I funding instruments: Cohesion Policy, supported by European Structural and Investment Funds (ESIF), Horizon Framework (Horizon 2020 and Horizon Europe) and Recovery and Resilience Facility (RRF) included in the Next Generation EU (Figure 2). 4 Figure 2. R&I funding programme included in the TEDv Source: Own elaboration. Information regarding the territorial concentration of these funds can be found across three dashboards: (i) the Regional dashboard, (ii) the Sectorial dashboard and (iii) the Comparison dashboard. Beyond EU funding indicators, the TEDv also reports socio-economic and demographic statistics displayed in the ‘Regional info-sheet’ dashboard (Figure 3). The TEDv enables the comparison of different EU funds in a particular territory and displays the contribution of each funding source towards the total R&D expenditure of that territory. This statistical information can be particularly valuable for policy-makers as it allows them to compare their territory’s relative position with respect to country and EU averages, as well as other regions within the EU. Figure 3. TEDv dashboards’ content Territorial R&I funding statistics Beyond EU funding indicators Regional dashboard Sectorial dashboard Comparison dashboard Regional info-sheet Provides a territory snapshot of the allocation of different R&I funding (ESIF, Horizon and RRF) Displays the sectorial concentration of different R&I funding (ESIF and Horizon) Allows comparing territories in terms of the allocation of different R&I funding (ESIF and Horizon) Compare regional performance over time with country and EU average, and provides information about the socio-economic and demographic position of the region (EUROSTAT) Source: Own elaboration. 5 Territorial Economic Data viewer: A data integration and visualization tool Anabela M. Santos* |corresponding author (Anabela.MARQUES-SAN[email protected]opa.eu) Andrea Conte* (Andre[email protected]opa.eu) Tauno Ojala* ([email protected]) Niels Meyer* (Niels.MEY[email protected]ropa.eu) Ilias Kostarakos* ([email protected]) Pietro Santoleri* ([email protected]) Yevgeniya Shevtsova* ([email protected]) Alicia de Quinto Notario* (Alicia.DE-QUINTO-NOT[email protected]ropa.eu) Francesco Molica* ([email protected]) Marie Lalanne* ([email protected]) * Joint Research Centre, European Commission, Seville, Spain Version: 15/05/2023 Abstract The present working paper aims to describe the data sources and methods used to develop the Territorial Economic Data viewer (TEDv), as well as to explain the purpose and usefulness of the different dashboards available in the current version of the tool. Additionally, this paper includes practical examples with policy lessons that can be drawn from the available information, as well as a glossary of the indicators within the TEDv. Keywords: Data integration; Territorial data visualisation; Policy monitoring. JEL Classification: O31; O20; C81, C82 ; O18. Disclaimer: The views expressed are purely those of the author(s) and may not in any circumstances be regarded as stating an official position of the European Commission. Acknowledgment: Authors are grateful to Juan Carlos del Rio (former colleague at the Joint Research Centre) for his support in the development of TEDv. Data behind the Working Paper: R&I funding statistics described in this Working Paper, as well as other original data are available for download in the Territorial Economic Data viewer (TEDv): https://web.jrc.ec.europa.eu/dashboard/TEDV/index.html 6 1. Context Access to relevant and timely data is crucial for supporting policy-decision making and improving the effectiveness of policy interventions. However, policy analysts face several challenges in finding the right data and integrating them into an accurate reporting system. Some of the main bottlenecks that policy experts encounter include different data sources with varying taxonomies, unstructured data, and limited time and resources. The TEDv was developed to support policymakers in monitoring the use of different Research and Innovation (R&I) funding programs. This task can be very daunting as information about the final beneficiaries and the territorial allocation of the different funds is not readily available in a single data repository. Government bodies, managing authorities, and/or stakeholders need to navigate through several data sources/webpages, sometimes with information translated into different formats and taxonomies. 1 The TEDv is a tool that addresses these challenges by integrating multiple data sources and presenting the information in a user-friendly format, thanks to Qlik Sense Enterprise (QSE) server hosted by the Joint Research Centre (JRC). The TEDv is the result of six main data-related activities developed within the Regional Economic Monitoring (REMO) pillar of the Territorial Data Analysis and Modelling (TEDAM) team, and developed to support the different phases of the policy decision-making process:  Data research: identifying needs, following data trends;  Data collection: data gathering from different sources, combining micro and macro-level data  Database construction: cleaning, harmonisation, and enrichment  Data analysis: producing derivate indicators to support monitoring and policy evaluation  Data visualisation: generating maps, graphs, and tailor-made statistics  Data sharing: data import for policy decision-making and research The present working paper starts by explaining the purpose and usefulness of the TEDv (section 2). Then, it describes the data sources (section 3) and methods behind the development of the TEDv (section 4). It also includes practical examples with policy insights that can be drawn from the available information (section 5), as well as a glossary and definitions of the indicators existing in the TEDv. 1 Such differences and the lack of a centralized data repository are mainly the result of the different governance model of the existing R&I funding programme. For instance, Cohesion policy operational programmes are managed at regional and/or national level, whereas Horizon Europe is centrally managed by the European Commission. 13 transitory shocks. One additional advantage of using longitudinal data is that they allow us to check whether the target region and its similar peers, experience similar dynamics in both levels and trends over time. To that end, we selected a 10-year interval spanning 2011-2020. 4 Before performing the matching procedure, we take averages over the entire period to attenuate any temporary shock, which may lead to misleading comparisons. Having defined the set of variables, we use a statistical matching approach to identify similar regions called the Mahalanobis Distance Matching (MDM) algorithm (King and Nielsen, 2019), a technique that is widely used to build control groups in observational studies (see, e.g., Cerqua and Pellegrini, 2022). The MDM allows us to identify, for a given target region, the regions that are as similar as possible based on the set of variables outlined in Table 2. In principle, this approach allows the selection of several peer regions. However, having a large number of peers would arguably defy the main goal of the exercise, as it would lead to identify regions whose degree of similarity with the target region is very small. As a result, for any given region we restrict the maximum number of potential peers to three. Additionally, we allow the algorithm to identify the three closest peers in absolute terms, and the three closest peers outside the country of the target region. This allows the option to identify peers that do not belong to the same country (see Appendix B for an example). Finally, it is important to stress that while this benchmarking tool makes it easier to conduct more appropriate comparisons across regions, it should not be regarded as a way to make causal statements or as a substitute for rigorous ex-post policy evaluation. 4.3. Data visualisation techniques Once all the statistical data are expressed in a common taxonomy (NUTS version 2021 and NACE codes), the Qlik Sense Enterprise server is used for data integration and visualisation. Then, territorial statistics are displayed in four dashboards with different functionalities, as described in the next section. 5. Description of the applicability of TEDv 5.1. Regional Dashboard The Regional Dashboard provides an overview of the allocation of different R&I funding (ESIF, Horizon, and RRF) for different territories. It combines data from EUROSTAT, Cohesion Open Data Platform (complemented by Kohesio data), and eCORDA, as explained in previous sections. The values of R&D statistics from EUROSTAT refer to the last year available for the programming period displayed in the 4 Data availability is higher for this period compared to longer time spans. To test the sensitivity of our approach, we experimented with alternative time-spans (e.g. using the programming period 2014-2020) and obtained fairly similar results. 14 dashboard for ESIF-R&I or Horizon framework (i.e. 2014-2020 or 2021-2027). The amount of R&I funds from RRF refers to the period 2021-2026, and it appears in the dashboard for both programming periods (2014-2020 and 2021-2027) for comparison purposes. In addition to reporting the cumulative value of ESIF R&I and H2020 funding programmes associated with selected projects or operations for a territory (country, NUTS-1 or NUTS-2 level regions), the dashboard also displays the average annual value of these funds and their estimated contribution to the total R&D expenditure in a territory. Furthermore, the dashboard also shows the relative size of the different R&D funds, where a value equal to one means that two funds are equivalent in terms of their size. Figure 4 illustrates an example of TEDv visualisation for Portugal and Figure 5 for the Portuguese region of Alentejo (PT18). Looking at the information displayed in Figure 4, we can see that in the 2014-2020 programming period, the contribution of the Cohesion Policy to finance Portugal’s R&D expenditure is higher than that of the Horizon framework. For instance, on average, ESIF-R&I funds contributed to 22% of R&D expenditure whereas H2020 contributed only 5.3% (graph in the middle and at the bottom of the dashboard). Consequently, the size of ESIF-R&I is about 4 times larger than that of the H2020 (graph on the right and at the bottom of the dashboard). The amount of R&I investment under the RRF is expected to be lower than that of ESIF-R&I for 2014-2020 but similar to that of H2020. Figure 5 reports the same information (except for RRF, since costs estimated are only available at the country-level) for the Portuguese NUTS 2-level region Alentejo (PT18). The region reveals to be less innovative than the country average, based on the R&D expenditure intensity expressed as % of GDP (0.78% versus 1.4%). Furthermore, the ESIF-R&I contributed, on average, to around 54% of the total R&D expenditures of the region, more than twice the value registered at the country-level. The annual average contribution of H2020 to R&D expenditure is around 4% and below the country average (5%). The size of the ESIF-R&I fund is about 13 times more than that of H2020. Figure 4. Example of TEDv visualisation: Regional Dashboard - Portugal Source: TEDv (extracted on 31/03/2023). 15 Figure 5. Example of TEDv visualisation: Regional Dashboard – Portuguese region of Alentejo (PT18) Source: TEDv (extracted on 31/03/2023). 5.2. Sectorial Dashboard The Sectorial Dashboard displays the concentration of different R&I funds (ESIF and Horizon) by economic activity (NACE classification), expressed in relative (% total) and absolute terms (EUR or the number of projects/operations). It also reports the sectorial concentration of the total annual R&D expenditures (EUR) in a territory, extracted from EUROSTAT (country total). Those data include R&D statistics from Business R&D expenditures (BERD) by economic activities. The territorial statistics displayed in this dashboard allow us to see if the different R&I funds show a similar sectorial concentration and whether they are similar to the territory trend in terms of R&D expenditures concentration. Figure 6 illustrates an example of the TEDv visualisation for Germany. For instance, it shows that even if the manufacturing industry is responsible for around 85% of the total Business R&D expenditure and 58% of the total R&D of the country, this economic activity is mainly financed by EU funds coming from Cohesion Policy programmes. Indeed, H2020 is mostly concentrated on specialised services (NACE section M) and education (NACE section P). Indeed, these activities captured around 87% of total H2020 funds. 16 Figure 6. Example of TEDv visualisation: Sectorial Dashboard – Germany Source: TEDv (extracted on 31/03/2023). 5.3. Comparison Dashboard The Regional Comparison allows comparing territories in terms of the allocation of different R&I funding (ESIF and Horizon). To this purpose, territorial statistics on R&I funding (ESIF and H2020) are expressed in per capita terms, % of total annual R&D expenditure, and in relative size ratio. The comparison between territories regarding the territorial statistics listed above is available under four options: 1) It shows the territorial statistics for all the EU countries (Figure 7) 2) It displays the territorial statistics for all the NUTS 2-level regions (see example in Figure 8) or NUTS 1-level regions in a selected country 3) It allows the user to own select the different territories to compare expressed in the same NUTS level (see example in Figure 9) 4) It allows the user to compare a NUTS 2-level region with the three most similar regions (irrespectively of the country) - predetermined in the TEDv (see example in Figure 10) or the three most similar regions outside the country. 17 Figure 7. Example of TEDv visualisation: Comparison dashboard – EU Member states Source: TEDv (extracted on 31/03/2023). Figure 8. Example of TEDv visualisation: Comparison dashboard – all Spanish regions Source: TEDv (extracted on 31/03/2023). 18 Figure 9. Example of TEDv visualisation: Comparison dashboard – user selection Source: TEDv (extracted on 31/03/2023). Figure 10. Example of TEDv visualisation: Comparison dashboard, Andalucia (ES61) – peers regions selection Source: TEDv (extracted on 31/03/2023). The fourth options available in this dashboard can be particularly useful for policymakers, for instance, to see how much competitive R&I funds (e.g. through H2020 of HE) the region has managed to attract compared to regions with similar characteristics. The example illustrated in Figure 10 shows that the Spanish region of Andalucía (ES61) has attracted less H2020 funding per capita comparing with its national peer (Comunitat Valenciana – ES52), but more than its peer regions located outside Spain (Campania – ITF3 – and Sicilia – ITG1). 19 5.4. Regional info-sheet The ‘Regional info-sheet’ dashboard reports regional statistics beyond R&I funding indicators, and aims to complement the information available in the other dashboards. It allows to compares the regional performance over time for some selected socioeconomic and demographic indicators with the country and EU average, as well as regarding well-know composite indicators like Regional Innovation Scoreboard (RIS) and Regional Competitiveness index (RCI). Those indicators are divided by four tabs, as described below:  Overview: ─ Classification of the region according to the Cohesion criteria in the period 2014-2020 (moredevelopped, less-developped or transition region) according to the Commission Implementing Decision (2014/99/EU); ─ Regional Innovation Scoreboard 5 : reports the category and ranking of the region (last year available) and its evolution over time ─ Regional competitiveness Index 6 : displays the ranking of the region (last year available)  Economic: ─ GDP per capita expressed in Purchasing Power Parity (PPP) and relative term face to EU27, where the EU27 is equal to 100 (EUROSTAT, nama_10r_2gdp) ─ Annual growth rates of the Total Factor Productivity: indicator measuring change in production’s efficiency and extracted from Kostarakos (2023) ─ Investment rate: estimated by the ratio between the capital investment (Gross Fixed Capital Formation – GFCF – EUROSTAT, nama_10r_2gva) and output (Gross Value Added – GVA – EUROSTAT, nama_10r_2gfcf)  Employment and skills: ─ Unemployment rate (EUROSTAT, lfst_r_lfu3rt): refers to the number of people unemployed (15 to 74 years of age) as a percentage of the labour force (active population); ─ Share of persons employed in science and technology (EUROSTAT, tgs00038): displays the total of human resources in science and technology (HRST) as a share of the active population in the age group 15-74. HRST refers to persons having successfully completed an education at the third level or being employed in science and technology. ─ Share of employment with tertiary education (EUROSTAT, lfst_r_lfe2emprtn): employment with an educational attainment level between 5 and 8 (tertiary education) according to the International Standard Classification of Education (ISCED 2011) over total employment from 15 to 64 years. 5 https://research-and-innovation.ec.europa.eu/statistics/performance-indicators/regional-innovation-scoreboard_en 6 https://ec.europa.eu/regional_policy/information-sources/maps/regional-competitiveness_en 20  Demographic: ─ Population density (EUROSTAT, demo_r_d3dens): displays the number of persons per square kilometer; ─ Old-age dependency ratio: reports the number of elderly people at an age when they are generally economically inactive (65 years old and over), compared to the number of people of working age (15-64 years old). Estimated using EUROSTAT - demo_r_d2jan; ─ Projected relative change of the population in 2030, 2040 and 2050 using as baseline scenario the population in 2019 (EUROSTAT, proj_19rp3) shows the estimated percentage change of population in comparison to 2019. When past time series is displayed, the dashboard allows the users to select the year they want to visualize. Figure 11, Figure 12 and Figure 13 show examples of the visualisation available for each of the Regional info-sheet dashboards using the case of the Romanian’s region Centru (RO12). For example, it shows that regions' per capita GDP is below the EU average but has improved over time (Figure 11 - bottom left graph). Furthermore, even though the job qualification in the region is below the EU, the regions' performance is above the country average (Figure 12 – bottom center and right graphs). Figure 11. Example of TEDv visualisation: Regional info-sheet – Economic dashboard, Romanian’s region Centru (RO12) Source: TEDv (extracted on 31/03/2023). 21 Figure 12. Example of TEDv visualisation: Regional info-sheet – Employment and skills dashboard, Romanian’s region Centru (RO12) Source: TEDv (extracted on 31/03/2023). Figure 13. Example of TEDv visualisation: Regional info-sheet – Demographic dashboard, Romanian’s region Centru (RO12) Source: TEDv (extracted on 31/03/2023). 6. Policy relevance of TEDv The present working paper provides a detailed description of the data and methodological approach used to develop the Territorial Economic Data viewer (TEDv) launched by the Joint Research Centre. The TEDv is the first available tool that combines statistical territorial information from different EU funding programmes in a single and coherent framework, thanks to the methodological effort on territorial and 22 sectorial/thematic allocations (via taxonomy conversions). This allows users to compare the size of different EU funding programmes and their contribution to total R&D expenditures, making it a valuable resource for analysts and policy-makers. Furthermore, the Comparison dashboard includes a territorial benchmarking tool that allows appropriate comparison across regions, providing useful information to support policy cycle and design. 29  RRP: Recovery and Resilience Plan  TC: Territorial Cooperation  TEDv: Territorial Economic Data viewer  TFP: Total Factor Productivity  TO: Thematic Objective  TO1: Thematic Objective 1 - Research and Innovation