Working With Eurostat Data"

In iotables: Reproducible Input-Output Economics Analysis, Economic and Environmental Impact Assessment with Empirical Data

knitr::opts_chunk$set(
  collapse = TRUE,
  comment = "#>"
)
library(iotables)
require(dplyr)

Symmetric input-output tables and their auxiliary tables are the most complex, structured statistical products that exit, because they contain relationships among 60x60 industry, the supply and the use of the GDP / GNP, and the income disturbing, trade and transport margins and tax receipts from these industries.

The Eurostat website contains the symmetric input-output data in long form. Working with this data requires a knowledge of the Eurostat SNA vocabulary to arrange the long data in the correct ordering of rows and columns. This is what the preprocessing functions of iotables handle.

Apart from handing a vast and not always well-documented vocabulary, the user is often confronted with "real-life" data issues, such as missing rows and columns in certain members states or spelling errors in the vocabulary. Many hours of testing and exception handling went into the current form of the processing functions, but there are further problems may show up. Most of the testing was done on product x product type SIOTs, because most member states use them. The functions work with industry x industry SIOTs, but further vocabulary exception may show up in their use. Please report these issues on github.

You are most likely to work with the product x product tables. For the availability of other data see ?iotables_download.

The analytical functions are presented at greater detail in the Germany 1990 vignette. The numbering of the equations refers to the Eurostat Manual of Supply, Use and Input-Output Tables (Eurostat Manual, Chapter 15.) The manual was prepared in 2008 under the ESA95 standards. Currently Eurostat uses the ESA2010 definitions, and some indicators in the manual are no longer available. In most uses this will not cause a problem.

Another very useful source of information on how to work with the tables Input-output analytical tables: methods and application to UK National Accounts. Since the United Kingdom at the time of writing the article was a member of the European Union, her national accounts follow the EU ESA standards.

Getting and processing Eurostat data

The following code, if specifically run (it will not run on default), will download the latest relevant data from the Eurostat data warehouse, and save it to a directory named ../not_included . The iotables package uses the other rOpenGov package Eurostat with additional processing of the rather complex SIOT bulk files.

The contents of the not_included directory can be found on GitHub, but they are not released and distributed with the package.

#Not run
not_included_directory <- file.path('..', 'not_included')
if ( ! dir.exists(not_included_directory) ) dir.create (not_included_directory)
#The contents of the 'not_included' directory can be found on GitHub, 
#but they are not released and distributed with the package.

naio_10_cp1700 <- iotables_download(
  "naio_10_cp1700", #SIOT
  data_directory = not_included_directory) 

# For inclusion in the package, the files must be smaller. 
# Reducing the size of the bulk files will not affect
# the demonstration.

naio_10_cp1700 <- naio_10_cp1700 %>%
  dplyr::filter ( geo %in% c("CZ", "SK")) %>%
  dplyr::filter ( year %in% c(2010, 2015))

#Conforming employment data both sexes from 15 years old, year 2015.
#prod_na vocabulary for product x product conformity
emp_cz <- employment_get(geo = "CZ", year = "2015", sex = "Total",
  age = "Y_GE15", labelling = "prod_na", 
  data_directory = not_included_directory,
  force_download = TRUE)

#Conforming employment data #both sexes from 15 years old, year 2017.
emp_sk <- employment_get(geo = "SK", year = "2017", sex = "Total",
  age = "Y_GE15", labelling = "prod_na",
  data_directory = not_included_directory,
  force_download = TRUE)

save (naio_10_cp1700, emp_sk, emp_cz, 
      file = file.path('..', 'inst', 'extdata',
                       'naio_10_product_x_product.rda'))

For a quicker building of the vignette, the data is retrieved from the not_included directory. The chunk above can reproduce this data file.

#load from pre-saved file to increase running speed
load (system.file('extdata', 
                  'naio_10_product_x_product.rda', 
                  package = 'iotables') )

In this vignette example the Czech (2015) national currency unit and the Slovak (2010) euro tables are created. Since the Slovak national currency is the euro, there is no difference between the Slovak national currency unit and euro tables.

cz_io <-  iotable_get ( labelled_io_data = naio_10_cp1700, 
                         source = "naio_10_cp1700", geo = "CZ", 
                         year = 2015, unit = "MIO_NAC", 
                         stk_flow = "TOTAL",
                         labelling = "short" )

sk_io <-  iotable_get ( labelled_io_data = naio_10_cp1700, 
                        source = "naio_10_cp1700", geo = "SK", 
                        year = 2010, unit = "MIO_EUR", 
                        stk_flow = "TOTAL",
                        labelling = "short" )

cz_input_flow <- input_flow_get( data_table = cz_io )

sk_input_flow <- input_flow_get( data_table = sk_io)

cz_output <- output_get( data_table = cz_io)
sk_output <- output_get( data_table = sk_io)

By default, total rows and columns are removed when creating use tables.

Analytic functions {#analytic-functions}

Input coefficients matrix

Iotables removes the columns and rows that are completely empty and creates the input coefficient matrix, which is used for most of the analytical functions.

The input_coefficient_matrix_create() function relies on the following equation. The numbering of the equations is the numbering of the Eurostat Manual.

(9) $a_{ij}$ = $X_{ij}$ / $x_j$ [recap: (43) is the same]

It checks the correct ordering of columns, and furthermore it fills up 0 values with 0.000001 to avoid division with zero.

input_coeff_matrix_cz <- input_coefficient_matrix_create(
  data_table = cz_io
)

input_coeff_matrix_sk <- input_coefficient_matrix_create(
  data_table = sk_io
)

knitr::kable(head(input_coeff_matrix_cz[,1:8]))

In the Czech SIOTs, they are removed by the statistical authority, so the Czech SIOTs appear smaller.

Most countries do not use the L_68A, CPA_U [Services provided by extraterritorial organisations and bodies] and CPA_T [Services of households as employers; undifferentiated goods and services produced by households for own use]industries, instead they use L_68B for the income component of real estates, and they do not calculate the In order to prevent division by zero errors, they are symmetrically removed from rows and columns. This will not change the results.

Creating the Leontief-matrix and its inverse {#leontieff-inverse}

The Leontief matrix is derived from Leontief equation system.

(19) $(I-A)x = y$

The Leontief matrix is defined as $(I-A)$ and it is created with the leontieff_matrix_create() function.

The Leontief inverse is (I-A)^-1^ and it is created with the leontieff_inverse_create() function from the Leontief-matrix.

The code chunk below prints a small part of the Czech Leontief-inverse.

L_cz <- leontieff_matrix_create( input_coeff_matrix_cz  )
I_cz <- leontieff_inverse_create( input_coeff_matrix_cz )

L_sk <- leontieff_matrix_create( input_coeff_matrix_sk  )
I_sk <- leontieff_inverse_create( input_coeff_matrix_sk )

knitr::kable(head(I_cz[,1:8]))

You can create the Leontief-matrix and its inverse from the output coefficient matrix, too, starting with output_coefficient_matrix_create() if you know what you are doing!

Direct effect indicators

The direct effects can be compared to intermediate production, domestic product or total supply.

The calculation follows the Eurostat Manual on p497-499

(60) $a_{ij}$ = $z_{ij}$ / $x_j$ [recap: (43) is the same]

$a_{ij}$ = input coefficient $z_{ij}$ = input of type i in sector j (i.e. products or capital or labor) $x_j$ = output of sector j

By default, direct_supply_effects_create() will compare inputs to total final demand / supply. You can make comparisons to total product or total output, too. The code below prints a part of the Czech direct effects rounded to 4 digits.

primary_inputs_cz <- coefficient_matrix_create(data_table = cz_io, 
                                              total = 'output', 
                                              return = 'primary_inputs') 

primary_inputs_sk <- coefficient_matrix_create(data_table = sk_io, 
                                              total = 'output', 
                                              return = 'primary_inputs')

direct_cz <- direct_effects_create( primary_inputs_cz, I_cz )  
direct_sk <- direct_effects_create( primary_inputs_sk, I_sk )  

knitr::kable (head(direct_cz[,1:8]), digits = 4)

Direct effects measure the initial, direct impact of the change in demand and supply for a product. When production goes up, it will create demand in all supply industries (backward linkages) and create opportunities in the industries that use the product themselves (forward linkages.)

This is not the total effect, because some of the increased production will translate into income, which in turn will be used to create further demand in all parts of the domestic economy. The total effect is characterized by multipliers.

Total effects {#total-effects}

Input multipliers

The input_multipliers_create function will create the various multipliers for each product.

(63) Z = B(I-A)^-1^

B = vector of input coefficients compared to final demand / supply.

Z = direct and indirect requirements for wages (or other income)

The calculation follows the Eurostat Manual p 499-502.

The code chunk below prints a part of the Czech multipliers, rounded to 4 digits.

primary_inputs_cz <- coefficient_matrix_create(data_table = cz_io, 
                                              total = 'output', 
                                              return = 'primary_inputs') 

primary_inputs_sk <- coefficient_matrix_create(data_table = sk_io, 
                                              total = 'output', 
                                              return = 'primary_inputs')

multipliers_cz <- input_multipliers_create( primary_inputs_cz, I_cz )  
multipliers_sk <- input_multipliers_create( primary_inputs_sk, I_sk ) 

knitr::kable (head(multipliers_cz[,1:8]), digits = 4)

Employment indicators and multipliers {#employment-multipliers}

The creation of the employment indicator is facilitated with the data processing function employment_get. The employment data as input data is not part of the Eurostat SIOT files, and the Eurostat employment data uses a more disaggregated structure. This function downloads and brings the employment data to conforming aggregate vector.

Other inputs, for example, CO2 emissions may be used, but they are likely to be need a manual aggregation. The helper function conforming_vector_create will create an empty vector that you can save as a .csv or Excel file and fill up manually with customary input data.

#New function is needed to add employment vector to SIOT
names ( emp_sk )[1] <- 'prod_na'
names ( emp_cz )[1] <- 'prod_na'

emp_indicator_sk <- rbind ( 
  sk_io[, 1:66], 
  emp_sk) %>% coefficient_matrix_create(., 
       return_part = 'primary_inputs') %>%
  filter ( prod_na == "employment_total" )

emp_indicator_cz <- full_join ( 
  cz_io, 
  emp_cz) %>% coefficient_matrix_create(., 
       return_part = 'primary_inputs') %>%
  filter ( prod_na == "employment_total" )


emp_effect_sk <- direct_effects_create( emp_indicator_sk, I_sk )  
emp_effect_cz <- direct_effects_create( emp_indicator_cz, I_cz )  

knitr::kable (emp_effect_cz[1:8], digits = 5)

#New function is needed to add employment vector to SIOT

emp_multiplier_sk <- input_multipliers_create( emp_indicator_sk, I_sk )  
emp_multiplier_cz <- input_multipliers_create( emp_indicator_cz, I_cz )  

knitr::kable (emp_multiplier_cz[1:8], digits=5)

Output multipliers {#output-multipliers}

output_multipliers_cz <- output_multiplier_create (input_coeff_matrix_cz)
output_multipliers_sk <- output_multiplier_create (input_coeff_matrix_sk)

knitr::kable (head(output_multipliers_cz[,1:8]), digits=4)

Interindustrial linkage analysis {#interindustrial-linkage-analysis}

Backward linkages {#backward-linkages}

Backward linkages show the effect on industries that are suppliers to the production (industry) in question.

cz_bw <- backward_linkages(I_cz)
sk_bw <- backward_linkages(I_sk)

knitr::kable (head(cz_bw[,1:8]), digits=4)

Forward linkages {#forward-linkages}

Forward linkages show the effects on other industries that use the product (industry output) at question as an input. Forward linkages can be derived from the ouput coefficient table.

output_coeff_cz <- output_coefficient_matrix_create( 
  io_table = cz_io, total = "tfu", digits = 4)

output_coeff_sk <- output_coefficient_matrix_create( 
  io_table = sk_io, total = "tfu")

knitr::kable (head(output_coeff_cz[,1:8]))

From the output coefficient matrix we can create the Leontief-matrix for outputs, its inverse, and summarize for forward linkages. These steps are taking place in the forward_linkages function.

cz_fw <- forward_linkages ( output_coeff_cz )
sk_fw <- forward_linkages( output_coeff_sk )

knitr::kable (head(cz_fw), digits=4)

Write results into Excel files

This code will not run, unless you run it separately. The resulting files can be used to check calculations in Excel or other application.

The contents of the not_included directory can be found on GitHub, but they are not released and distributed with the package.

require(xlsx)
cz_file_name <- file.path("..", "not_included", "CzechRep_test.xlsx")
#Czech Republic data
xlsx::write.xlsx ( cz_io, file = cz_file_name, sheetName = "io_table",
                   col.names=TRUE, row.names=TRUE, append=FALSE)
xlsx::write.xlsx ( cz_output, file = cz_file_name, sheetName = "cz_output",
                   col.names=TRUE, row.names=TRUE, append=TRUE)
xlsx::write.xlsx ( input_coeff_matrix_cz, file = cz_file_name, 
                   sheetName = "input_coeff_matrix_cz",
                   col.names=TRUE, row.names=TRUE, append=TRUE)
xlsx::write.xlsx ( L_cz, file = cz_file_name, sheetName = "L_cz",
                   col.names=TRUE, row.names=TRUE, append=TRUE)
xlsx::write.xlsx ( I_cz, file = cz_file_name, sheetName = "I_cz",
                   col.names=TRUE, row.names=TRUE, append=TRUE)
xlsx::write.xlsx ( direct_cz, file = cz_file_name, 
                   sheetName = "direct_cz",
                   col.names=TRUE, row.names=TRUE, append=TRUE)
xlsx::write.xlsx ( multipliers_cz, file = cz_file_name, 
                   sheetName = "multipliers_cz",
                   col.names=TRUE, row.names=TRUE, append=TRUE)
xlsx::write.xlsx ( emp_effect_cz, file = cz_file_name, 
                   sheetName = "emp_effect_cz_2015",
                   col.names=TRUE, row.names=TRUE, append=TRUE)
xlsx::write.xlsx ( emp_indicator_cz, file = cz_file_name, 
                   sheetName = "emp_indicator_cz",
                   col.names=TRUE, row.names=TRUE, append=TRUE)
xlsx::write.xlsx ( emp_multiplier_cz, file = cz_file_name, 
                   sheetName = "emp_multiplier_cz",
                   col.names=TRUE, row.names=TRUE, append=TRUE)
xlsx::write.xlsx ( cz_bw, file = cz_file_name, 
                   sheetName = "cz_backward_linkages",
                   col.names=TRUE, row.names=TRUE, append=TRUE)
xlsx::write.xlsx ( output_coeff_cz, file = cz_file_name, 
                   sheetName = "output_coeff_sk",
                   col.names=TRUE, row.names=TRUE, append=TRUE)
xlsx::write.xlsx ( cz_fw, file = cz_file_name, 
                   sheetName = "cz_forward_linkages",
                   col.names=TRUE, row.names=TRUE, append=TRUE)
xlsx::write.xlsx ( output_multipliers_cz, file = cz_file_name, 
                   sheetName = "output_multipliers_cz",
                   col.names=TRUE, row.names=TRUE, append=TRUE)


sk_file_name <- file.path("..", "not_included", "SlovakRep_test.xlsx")
#Czech Republic data
xlsx::write.xlsx ( sk_io, file = sk_file_name, sheetName = "io_table",
                   col.names=TRUE, row.names=TRUE, append=FALSE)
xlsx::write.xlsx ( sk_output, file = sk_file_name, sheetName = "sk_output",
                   col.names=TRUE, row.names=TRUE, append=TRUE)
xlsx::write.xlsx ( input_coeff_matrix_sk, file = sk_file_name, 
                   sheetName = "input_coeff_matrix_sk",
                   col.names=TRUE, row.names=TRUE, append=TRUE)
xlsx::write.xlsx ( L_sk, file = sk_file_name, sheetName = "L_sk",
                   col.names=TRUE, row.names=TRUE, append=TRUE)
xlsx::write.xlsx ( I_sk, file = sk_file_name, sheetName = "I_sk",
                   col.names=TRUE, row.names=TRUE, append=TRUE)
xlsx::write.xlsx ( direct_sk, file = sk_file_name, 
                   sheetName = "direct_sk",
                   col.names=TRUE, row.names=TRUE, append=TRUE)
xlsx::write.xlsx ( multipliers_sk, file = sk_file_name, 
                   sheetName = "multipliers_sk",
                   col.names=TRUE, row.names=TRUE, append=TRUE)
xlsx::write.xlsx ( emp_effect_sk, file = sk_file_name, 
                   sheetName = "emp_effect_sk_2015",
                   col.names=TRUE, row.names=TRUE, append=TRUE)
xlsx::write.xlsx ( emp_indicator_sk,file = sk_file_name, 
                   sheetName = "emp_indicator_sk",
                   col.names=TRUE, row.names=TRUE, append=TRUE)
xlsx::write.xlsx ( emp_multiplier_sk, file = sk_file_name, 
                   sheetName = "employment_multipliers_sk",
                   col.names=TRUE, row.names=TRUE, append=TRUE)
xlsx::write.xlsx ( sk_bw, file = sk_file_name, 
                   sheetName = "sk_backward_linkages",
                   col.names=TRUE, row.names=TRUE, append=TRUE)
xlsx::write.xlsx ( output_coeff_sk, file = sk_file_name, 
                   sheetName = "output_coeff_sk",
                   col.names=TRUE, row.names=TRUE, append=TRUE)
xlsx::write.xlsx ( sk_fw, file = sk_file_name, 
                   sheetName = "sk_forward_linkages",
                   col.names=TRUE, row.names=TRUE, append=TRUE)
xlsx::write.xlsx ( output_multipliers_sk, file = sk_file_name, 
                   sheetName = "output_multipliers_sk",
                   col.names=TRUE, row.names=TRUE, append=TRUE)

iotables documentation built on Sept. 24, 2022, 5:05 p.m.