Working With Eurostat Data

Daniel Antal, CFA

2021-12-21

library(iotables, quietly = TRUE)
library(dplyr, quietly = TRUE)

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 on vignette
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) 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, but you could create Czech koruna tables for Czechia.

The Slovak SIOT table contains empty columns and rows. They must be removed to avoid division by zero errors.

cz_io <- iotable_get( labelled_io_data = naio_10_cp1700, 
                       source = "naio_10_cp1700", geo = "CZ", 
                       year = 2015, unit = "MIO_EUR", 
                       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. You need to remove the columns and rows that are completely empty, or any row our column that has missing (NA or NaN) values. For example, when a country like Slovakia leaves the CPA_L68A technical roww/column pair empty, you must remove it consistently from all vectors and matrixes, also from data that you are joining for further economic or environmental impact analysis. The helper function empty_remove() removes systematically such rows and columns from symmetric matrixes. Beware not to use it on tables that contain missing values by definition, or tables that are not necessarily symmetric. The function is intended to be used in the first quadrant of the system of input-output tables and for conforming matrices or vectors.

# Remove the pre-processed naio_10_cp1700
rm(naio_10_cp1700)

Analytic functions

Input coefficients matrix

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

  1. \(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) 
#> Columns and rows of CPA_L68A, CPA_T, CPA_U, P6_U2, P6_U3 are all zeros and will be removed.
head(input_coeff_matrix_cz[,1:8])
#> # A tibble: 6 x 8
#>   prod_na     CPA_A01  CPA_A02 CPA_A03   CPA_B `CPA_C10-12` `CPA_C13-15` CPA_C16
#>   <chr>         <dbl>    <dbl>   <dbl>   <dbl>        <dbl>        <dbl>   <dbl>
#> 1 CPA_A01    0.125    0.00610  0.0220  4.68e-4     0.305        0.0433   3.12e-3
#> 2 CPA_A02    0.00280  0.130    0.00674 2.31e-3     0.000262     0.000300 2.44e-1
#> 3 CPA_A03    0        0        0       0           0.000723     0        0      
#> 4 CPA_B      0.000712 0.00279  0.184   4.64e-2     0.000568     0.00108  2.32e-4
#> 5 CPA_C10-12 0.148    0.000392 0.0494  1.46e-4     0.154        0.000407 2.45e-4
#> 6 CPA_C13-15 0.00487  0.0174   0.00601 7.74e-3     0.00142      0.288    1.32e-3

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 Leontieff-matrix and its inverse

The Leontieff matrix is derived from Leontieff equation system.

  1. \((I-A)x = y\)

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

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

The code chunk below prints a small part of the Czech Leontieff-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 )
head(I_cz[,1:8])
#>      prod_na      CPA_A01      CPA_A02    CPA_A03        CPA_B   CPA_C10-12
#> 1    CPA_A01 1.2259330317 1.459415e-02 0.05564368 7.767767e-03 0.4477719074
#> 2    CPA_A02 0.0074121989 1.158470e+00 0.01243268 6.442403e-03 0.0062998799
#> 3    CPA_A03 0.0001601342 5.042087e-06 1.00005311 4.723359e-06 0.0009174195
#> 4      CPA_B 0.0613082178 2.680584e-02 0.25150997 1.101021e+00 0.0515453344
#> 5 CPA_C10-12 0.2183090404 5.326012e-03 0.07114469 4.323450e-03 1.2639389493
#> 6 CPA_C13-15 0.0164669149 3.397020e-02 0.01922473 1.926643e-02 0.0143366983
#>     CPA_C13-15      CPA_C16
#> 1 8.415854e-02 1.606435e-02
#> 2 4.586325e-03 3.595805e-01
#> 3 1.663384e-05 7.056128e-06
#> 4 5.124313e-02 4.873310e-02
#> 5 1.920143e-02 6.672208e-03
#> 6 1.413920e+00 1.848414e-02

You can create the Leontieff-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 Manaul on p497-499

  1. \(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')
#> Columns and rows of CPA_L68A, CPA_T, CPA_U, P6_U2, P6_U3 are all zeros and will be removed.

direct_cz <- direct_effects_create( primary_inputs_cz, I_cz )  
direct_sk <- direct_effects_create( primary_inputs_sk, I_sk )
head(direct_cz[,1:8])
#>         prod_na    CPA_A01      CPA_A02    CPA_A03       CPA_B  CPA_C10-12
#> 1  TOTAL_effect  1.6834247  1.095244321  1.6880674  1.30655120  2.01051312
#> 2     P7_effect  1.1503990  0.533972473  2.6114730  3.71383337  1.47400620
#> 3     D1_effect  0.4263570  0.260810128  0.5585327  0.51398773  0.43172379
#> 4    D11_effect  0.3256564  0.199665095  0.4213977  0.37700140  0.32814992
#> 5 D29X39_effect -0.1630669 -0.008342165 -0.1921511 -0.01182259 -0.05942294
#> 6   P51C_effect  0.2270748  0.146971927  0.2734206  0.26267433  0.21053075
#>    CPA_C13-15      CPA_C16
#> 1  1.74031817  1.834648606
#> 2  2.79523809  0.975771847
#> 3  0.47200827  0.366969888
#> 4  0.35574581  0.279190723
#> 5 -0.01322804 -0.003250313
#> 6  0.21048618  0.182377900

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

Input multipliers

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

  1. 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') 
#> Columns and rows of CPA_L68A, CPA_T, CPA_U, P6_U2, P6_U3 are all zeros and will be removed.

not_removed_cols <- names(primary_inputs_sk)[names(primary_inputs_sk) %in% names(I_sk)] 

multipliers_cz <- input_multipliers_create( 
  primary_inputs_cz[,names(primary_inputs_cz) %in% names(I_cz)], I_cz )  
multipliers_sk <- input_multipliers_create( primary_inputs_sk[not_removed_cols], I_sk ) 
head(multipliers_cz[,1:8])
#>                    prod_na  CPA_A01  CPA_A02  CPA_A03    CPA_B  CPA_C10-12
#> 1 <environment>_multiplier 2.734894 2.481801 2.659809 2.677425    2.675645
#> 2 <environment>_multiplier 4.381010 5.310385 2.335304 1.287826    2.490763
#> 3 <environment>_multiplier 2.580705 2.726267 2.064126 1.686937    3.826019
#> 4 <environment>_multiplier 2.556156 2.699112 2.048389 1.722373    3.849046
#> 5 <environment>_multiplier 1.222226 1.452504 1.053472 1.160912 1708.913018
#> 6 <environment>_multiplier 2.418444 2.434447 2.223010 1.825428    4.703837
#>   CPA_C13-15   CPA_C16
#> 1   2.672010  2.517172
#> 2   1.891608  4.446161
#> 3   2.530682  3.316057
#> 4   2.544464  3.316171
#> 5   7.173591 -6.958898
#> 6   2.967467  3.851769

Employment indicators and 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.

Whilst in the previous comparisons the currency units and other differences between Czechia and Slovakia had little effect, they are important in employment effects. One million euro increase in demand will create more jobs where workers are paid significantly less (the increased turnover buys more input.) And of course, one million korunas have a very different demand effect than one million euros — this is why we chose to use the Czech euro tables.

#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)  
vector_transpose (emp_effect_cz, values_to ="employment_effect_cz") %>%
  left_join ( vector_transpose(emp_effect_sk, values_to = "employment_effect_sk"), by = 'nace_r2' ) %>%
  mutate ( across(starts_with("employmnet"), function(x) x*1000)) %>%
  arrange ( -.data$employment_effect_sk) %>%
  top_n(8)
#> Selecting by employment_effect_sk
#> # A tibble: 8 x 3
#>   nace_r2    employment_effect_cz employment_effect_sk
#>   <chr>                     <dbl>                <dbl>
#> 1 CPA_Q87_88               0.0849               0.194 
#> 2 CPA_I                    0.0433               0.0819
#> 3 CPA_H53                  0.0529               0.0780
#> 4 CPA_P                    0.0504               0.0728
#> 5 CPA_R93                  0.0465               0.0658
#> 6 CPA_S94                  0.0341               0.0588
#> 7 CPA_N80-82               0.0443               0.0586
#> 8 CPA_Q86                  0.0388               0.0532

The sector Q87_88, which stands for Residential care services; social work services without accommodation creates the highest employment with a million euro of increase in output. In Slovakia, the employment effect is always higher, which may reflect the the lower wage level of the Slovak economy. The class I, accommodation services has the highest employment effect in Slovakia, which is not suprising, these services require plenty of human labor and cannot be automated. The H53 refers to postal services.

After the direct effects, we turn our attention to multiplied effects, which take into consideration the extra employment among suppliers, and possible forward linkages where the each sector is a supplier itself.

emp_multiplier_sk <- input_multipliers_create(emp_indicator_sk[not_removed_cols], I_sk)  
emp_multiplier_cz <- input_multipliers_create(
  emp_indicator_cz[,names(emp_indicator_cz) %in% names(I_cz)], 
  I_cz )  
vector_transpose (emp_multiplier_cz, values_to ="employment_multiplier_cz") %>%
  left_join ( vector_transpose(emp_multiplier_cz, 
                               values_to = "employment_multiplier_sk"), by = 'nace_r2' ) %>%
  mutate ( across(starts_with("employmnet"), function(x) x*1000)) %>%
  arrange ( -.data$employment_multiplier_sk) %>%
  top_n(8)
#> Selecting by employment_multiplier_sk
#> # A tibble: 8 x 3
#>   nace_r2  employment_multiplier_cz employment_multiplier_sk
#>   <chr>                       <dbl>                    <dbl>
#> 1 CPA_C19                     20.7                     20.7 
#> 2 CPA_L68B                     6.00                     6.00
#> 3 CPA_H51                      5.61                     5.61
#> 4 CPA_D                        5.48                     5.48
#> 5 CPA_N79                      4.98                     4.98
#> 6 CPA_C29                      4.94                     4.94
#> 7 CPA_C26                      4.79                     4.79
#> 8 CPA_H52                      4.79                     4.79

From the previous list, only postal services remain. Postal workers are underpaid, and they deliver many letters for an extra million euros, but postal services have little suppliers, and they represent a small value in the value chain of more complex services.

CPA_C19 Manufacture of coke and refined petroleum products is on the top of multiplied effects.

Output multipliers

output_multipliers_cz <- output_multiplier_create (input_coeff_matrix_cz)
output_multipliers_sk <- output_multiplier_create (input_coeff_matrix_sk %>% empty_remove())
vector_transpose (emp_multiplier_cz, 
                  values_to ="employment_multiplier_cz") %>%
  arrange( -.data$employment_multiplier_cz ) %>%
  top_n(5)
#> Selecting by employment_multiplier_cz
#> # A tibble: 5 x 2
#>   nace_r2  employment_multiplier_cz
#>   <chr>                       <dbl>
#> 1 CPA_C19                     20.7 
#> 2 CPA_L68B                     6.00
#> 3 CPA_H51                      5.61
#> 4 CPA_D                        5.48
#> 5 CPA_N79                      4.98

Interindustrial linkage analysis

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)
#random sample
set.seed(123)
vector_transpose (cz_bw, 
                  values_to ="backward_linkages") %>%
  arrange( -.data$backward_linkages) %>%
  sample_n(5)
#> # A tibble: 5 x 2
#>   nace_r2 backward_linkages
#>   <chr>               <dbl>
#> 1 CPA_D                2.66
#> 2 CPA_F                2.97
#> 3 CPA_N77              2.08
#> 4 CPA_C22              2.99
#> 5 CPA_C29              3.54

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")
#> Columns and rows of CPA_L68A, CPA_T, CPA_U, P6_U2, P6_U3 are all zeros and will be removed.
output_coeff_cz[,1:6]
#>       prod_na CPA_A01 CPA_A02 CPA_A03  CPA_B CPA_C10-12
#> 1     CPA_A01  0.2407  0.0028  0.0003 0.0003     0.8878
#> 2     CPA_A02  0.0281  0.3063  0.0005 0.0069     0.0040
#> 3     CPA_A03  0.0000  0.0000  0.0000 0.0000     0.0777
#> 4       CPA_B  0.0028  0.0025  0.0051 0.0534     0.0033
#> 5  CPA_C10-12  0.0818  0.0001  0.0002 0.0000     0.1279
#> 6  CPA_C13-15  0.0097  0.0082  0.0001 0.0046     0.0043
#> 7     CPA_C16  0.0091  0.0201  0.0000 0.0061     0.0045
#> 8     CPA_C17  0.0014  0.0007  0.0001 0.0005     0.0633
#> 9     CPA_C18  0.0235  0.0065  0.0000 0.0047     0.1925
#> 10    CPA_C19  0.0907  0.0042  0.0001 0.0116     0.0311
#> 11    CPA_C20  0.0621  0.0009  0.0001 0.0086     0.0209
#> 12    CPA_C21  0.0060  0.0000  0.0000 0.0000     0.0016
#> 13    CPA_C22  0.0049  0.0023  0.0000 0.0065     0.0218
#> 14    CPA_C23  0.0025  0.0014  0.0000 0.0046     0.0212
#> 15    CPA_C24  0.0030  0.0007  0.0011 0.0009     0.0025
#> 16    CPA_C25  0.0007  0.0013  0.0000 0.0075     0.0057
#> 17    CPA_C26  0.0001  0.0001  0.0000 0.0010     0.0001
#> 18    CPA_C27  0.0004  0.0009  0.0000 0.0012     0.0001
#> 19    CPA_C28  0.0092  0.0015  0.0003 0.0085     0.0010
#> 20    CPA_C29  0.0012  0.0005  0.0000 0.0000     0.0002
#> 21    CPA_C30  0.0002  0.0000  0.0001 0.0006     0.0015
#> 22 CPA_C31_32  0.0002  0.0005  0.0000 0.0001     0.0049
#> 23    CPA_C33  0.0702  0.0060  0.0007 0.0175     0.0464
#> 24      CPA_D  0.0176  0.0005  0.0000 0.0182     0.0405
#> 25    CPA_E36  0.0581  0.0145  0.0017 0.0022     0.0473
#> 26 CPA_E37-39  0.0119  0.0041  0.0001 0.0041     0.0182
#> 27      CPA_F  0.0045  0.0035  0.0000 0.0012     0.0016
#> 28    CPA_G45  0.0372  0.0270  0.0000 0.0039     0.0348
#> 29    CPA_G46  0.0384  0.0041  0.0001 0.0056     0.1043
#> 30    CPA_H49  0.0103  0.0022  0.0000 0.0132     0.0350
#> 31    CPA_H50  0.0114  0.0010  0.0000 0.0000     0.0372
#> 32    CPA_H51  0.0001  0.0000  0.0000 0.0000     0.0007
#> 33    CPA_H52  0.0212  0.0106  0.0000 0.0338     0.0101
#> 34    CPA_H53  0.0326  0.0091  0.0000 0.0027     0.0222
#> 35      CPA_I  0.0021  0.0010  0.0000 0.0004     0.0020
#> 36    CPA_J58  0.0066  0.0001  0.0000 0.0001     0.0012
#> 37 CPA_J59_60  0.0002  0.0000  0.0000 0.0000     0.0088
#> 38    CPA_J61  0.0041  0.0042  0.0001 0.0008     0.0040
#> 39 CPA_J62_63  0.0027  0.0017  0.0000 0.0003     0.0099
#> 40    CPA_K64  0.1086  0.0161  0.0002 0.0094     0.0317
#> 41    CPA_K65  0.0306  0.0017  0.0003 0.0011     0.0038
#> 42    CPA_K66  0.0005  0.0009  0.0000 0.0000     0.0009
#> 43   CPA_L68B  0.0016  0.0009  0.0001 0.0003     0.0066
#> 44 CPA_M69_70  0.0232  0.0090  0.0001 0.0040     0.0560
#> 45    CPA_M71  0.0089  0.0027  0.0000 0.0157     0.0023
#> 46    CPA_M72  0.0001  0.0000  0.0000 0.0000     0.0003
#> 47    CPA_M73  0.0057  0.0012  0.0001 0.0051     0.3842
#> 48 CPA_M74_75  0.8115  0.0826  0.0002 0.0567     0.2855
#> 49    CPA_N77  0.0047  0.0014  0.0000 0.0163     0.1131
#> 50    CPA_N78  0.1672  0.0045  0.0062 0.0090     0.7740
#> 51    CPA_N79  0.0016  0.0016  0.0000 0.0000     0.0002
#> 52 CPA_N80-82  0.0708  0.0187  0.0001 0.0491     0.0912
#> 53      CPA_O  0.0035  0.0003  0.0000 0.0002     0.0006
#> 54      CPA_P  0.0012  0.0005  0.0000 0.0000     0.0006
#> 55    CPA_Q86  0.0001  0.0000  0.0000 0.0000     0.0008
#> 56 CPA_Q87_88  0.0000  0.0000  0.0000 0.0000     0.0000
#> 57 CPA_R90-92  0.0004  0.0001  0.0000 0.0000     0.0000
#> 58    CPA_R93  0.0001  0.0001  0.0000 0.0000     0.0002
#> 59    CPA_S94  0.0005  0.0002  0.0000 0.0001     0.0106
#> 60    CPA_S95  0.0027  0.0046  0.0000 0.0004     0.0361
#> 61    CPA_S96  0.0002  0.0000  0.0000 0.0001     0.0006

From the output coefficient matrix we can create the Leontieff-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)
head(cz_fw)
#>      prod_na forward_linkages
#> 1    CPA_A01         5.157401
#> 2    CPA_A02        13.975787
#> 3    CPA_A03         1.178657
#> 4      CPA_B        29.423868
#> 5 CPA_C10-12         1.915714
#> 6 CPA_C13-15         4.940868

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)