How to use the Updating module
After connecting to ESPON server, an expert user will be able to launch the command scripts. A list of all ESPON Base indicators with their unique identifiers (ind_id) as stored in he ESPON database can be found in Table 4.
The execution procedure will be as follow:
python manage.py 1st_insert_metadata –indicator_id <ind_id>
This script will read metadata from ESPON 2020 Database and will populate the majority of all fields in the tdta_metadata table. This is achieved through a BaseIndicator class (in Updating/BaseIndicator.py) that populates all fields except for number_of_null
Run 2nd_download_eurostat.py script
python manage.py 2nd_download_eurostat –indicator_id <ind_id>
This script will start the downloading process of tsv files from Eurostat downloading services:
- gets the name of the Eurostat dataset as read from a EUROSTAT_EQUIVALENCES dictionary key (eurostat_file) in eurostat_equivalences.py script
- checks the lastest version of downloaded tsv file (by reading the metadata.txt file with latest date). If current date (Y-M-Dformat), tsv file is not downloaded; otherwise, is downloaded
- if downloaded, moves those tsv files to eurostat directory with a created directory according to some categories (DEMOGRAPHY, ECONOMY, EDUCATION, EMPLOYMENT, ENERGY, ITS, R&D, SOCIETY)
Run 3rd_insert_eurostat.py script
python manage.py 3rd_insert_eurostat –indicator_id <ind_id>
This script will populate the tdta_eurostat table that will be used in the Updating application to get data from Eurostat and compare with pair-wise ESPON indicator so as to get the so-called virtual changes (later with 6th_create_EUS.py script)
Run 4th_insert_nulls.py script (optional)
python manage.py 4th_insert_nulls --indicator_id <ind_id> --country_code <ctr_code>
This script will populate the tdta_nulls table that will be useful to get an idea of existing nulls in Base Indicators. Moreover, a field in tdta_metadata called number_nulls is populated after this scripts is run.
Run 5th_insert_mp script
python manage.py 5th_insert_mp --indicator_id <ind_id> --country_code <ctr_code> This script will populate the tdta_missing_pattern table that will be useful to get the missing pattern for a given ESPON indicator and a given country. This information will be very valuable later, when 6th_create_EUS.py script is run. One completed, there is no need to re-run until a new bunch update of Base Indicator is injected in ESPON database. These five first scripts have only to be launched once and this is the only requirement to start with the updating procedure.
Run 6th_create_EUS.py script
python manage.py 6th_create_EUS --indicator_id <ind_id> --country_code <ctr_code> --create_zip True
This script will create the so-called Excel Updating Suite that consists of a zip file with an Excel Dashboard and a bunch of files/macros, gathering information from Updating database (previous steps). The location of created zips is eus/zips folder, with either subpath to country-basis folder (for instance, inds/145/By_Country/) if country_code parameter, or subpath to all-country-basis folder (for instance, inds/145/All_countries/).
These macros are generated from parametrised python templates that are located in the macros/templates folder of the Updating module. See Table 2
Once expert user has built the EUS zip files for all countries for a given indicator in the server, an accessible point to be downloaded will be ready. With all Excels files and macros in his local machine, the updating procedure controlled by an expert user can be start in a local workstation following the workflow as specified in the Table 3.
When this updating procedure is finished, a local sqlite3 file with all virtual changes for all the countries should be generated (see step 12, Table 3) and the expert user should upload that file to a folder (db_updates) in the ESPON server.
Run 7th_importer2ESPONDB.py script
python manage.py 7th_importer2ESPONDB --indicator_id <ind_id>
This script will read the uploaded sqlite3 file and all virtual changes will be integrated in the final ESPON database. A txt report is generated and stored in database containing relevant information about data to be inserted in ESPON database.
Table 3. Sequential steps in the Updating workflow to updating a base indicator
Preparation of the data for the update (creation of the Excel Updating Suite)
After selecting the indicator to be updated, a process is run on the server to generate an Excel Updating Suite for each country, containing, in different sheets, the current indicator figures, the virtual changes derived from the comparison with Eurostat data, and a specific sheet with macro buttons to launch the following steps. Those Excel sheets are downloaded from the server within a ZIP file.
Detection of potential changes from Eurostat data, which are compliant with the spatial and thematic constraints of the specific indicator, when estimated data are involved.
After unzipping the Excel sheets, the process starts with one of the countries. In order to detect the potential and compliant changes, the corresponding macro should be run from the Excel Updating Suite, i.e. by pressing the Launch button
Creation of spatial and thematic validation matrices for the whole time-series.
In order to create the matrices, the corresponding buttons (Spatial matrix and Thematic matrix) in the Excel Updating Suite should be pressed.
Creation of supportive charts (line plots) of the indicator time-series.
Optional. In case of need, the expert in charge of the update can generate automatic line plots of the data, to have a graphical overview of the indicator time-series by pressing the Charts button.
Review of the resulting matrices to decide eventually if potential changes should be integrated or rejected.
The matrices provide the deviation of potential changes with regard to the spatial and thematic rules. It is up to the expert in charge of the update, to decide whether a specific deviation is acceptable or not.
Manual editing of potential changes to be rejected or modified.
All the potential changes not edited (or removed) in the corresponding Excel sheet will be eventually actual changes in the database. If some changes are considered as unacceptable, should be removed from the corresponding Excel sheet at this stage.
Detection of potential outliers in ESPON time series.
In case of need, the expert in charge of the update can launch two algorithms to detect potential outliers in the ESPON time series by pressing the Outliersbutton.
Save potential changes to the local DB.
By means of the Save button, the potential changes that remain in the Excel sheets are stored in a local database.
Introduction of additional figures from other sources and save the changes to the local DB.
There is the possibility to add manually potential changes coming from other sources (including estimated values). This is done in this step by pressing the corresponding buttons (Start manual session and Detect manual changes). If manual changes have been introduced, the potential changes need to be saved to the local DB again.
Repeat processes 1 to 9 for all the countries covered by the specific indicator.
Prepare a single file for the specific indicator including all the countries.
Upload the file to the ESPON DB Server and actually update the indicator.
Table 4. ESPON Base Indicators and unique indicator identifier as stored in ESPON database
Population, total, by broad age (0-14, 14-65, >65) groups and gender
Life expectancy, by age, gender
Population, total, by broad age groups (by 5s) and gender
Old age dependence ratio
Deaths by age groups and gender (NUTS2)
Deaths by age groups and gender (NUTS3)
Natural Change of population
Total Net Migration
Employment rates by sex & broad age groups (%)
Employment by sex & broad age groups (1000)
Employment by age, economic activity (Econ. sections C, F, I)
Unemployment by sex & age (1000)
Unemployment by sex & age (%)
Employment by age, economic activity (Econ. sections A, F and K)
Long term unemployment
Early leavers from education and training (18-24 years) by sex
Participation rate in education and training (age group 25 to 64 years)
Population aged 25-64, 30-34 by educational attainment level, sex (%)
Disposable income of private households
Gross value added at basic prices, million EURO
Households with broandband access (percentage of households)
Gross domestic product (GDP) at current market prices by Million PPS
Gross domestic product (GDP) at current market prices by Million Euro
Gross domestic product (GDP) at current market prices by Purchasing Power Standard per inhabitant
Share of energy from renewable sources (in gross final energy consumption)
Greenhouse gas emissions by source sector (EEA)
Primary energy consumption
Energy intensity of the economy
People at risk of poverty or social exclusion (% of total population)
Severe material deprivation rate by NUTS2 regions (% of population)
People living in households with very low work intensity (population aged 0 to 59 years)
At-risk-of-poverty rate by NUTS2 regions (% of population)
Human resources in science and technology
Patent applications to the EPO by priority year by NUTS 3 regions per million inhabitants
Total intramural R & D expenditure (GERD) by sectors of performance (Percentage of GDP)
Total R&D personnel and researchers by sectors of performance, sex (Percentage of active population)
Individuals who used the internet for interaction with public authorities
Individuals who have never used a computer (% of population)
Individuals who ordered goods or services over the Internet for private use (% of population)