Working with Crotian Symmetric Input-Output Tables

Daniel Antal, CFA

2018-01-30

Croatia

To show how to work with other IO tables and other data, we include the latest SIOT and employment statistics from Croatia with the package. Croatia joined the EU after 2010 and the country’s SIOTs are following the Eurostat guidelines but are not available on Eurostat yet. The national Symmetric Input-Output Tables (SIOTs) are created every five years, with a very long statistical work. For most EU countries the latest SIOT available is based on the 2010 structure of the underlying national economy. Because Croatia was not a member state at the time, it uses a temporary SIOT that is identical in most aspects to the Eurostat standard SIOTs.

There are some technical differences that can be obtains from DZS. The most important practical differences are:

The data is provided in the same long form that is used by Eurostat. The data file contains all the three parts of the SIOT and aggregation rows.

The strategy to work with SIOTs in Excel format is the following: - Read in the Excel table with any suitable R excel reader. - Use t_rows2 columns to describe the rows of the table, use t_cols2 to describe the columns of the table. - You can additionally add longer labels called t_rows2_lab and t_cols2_lab. - Bring the data to a tidy, long-form, for example with plyr or dplyr functions. - Filter out the part of the SIOT you need for your matrix equations with the iotables pre-processing functions.

You should aim at something similar to croatia_2010_1700, the table 1700 for the year 2010.

library (iotables); library (dplyr); require (knitr)
hr_io_1800 <- iotable_get ( source = "croatia_2010_1800", geo = "HR",
                         year = 2010, unit = "T_NAC", 
                         labelling  = "short")


knitr::kable(head(hr_io_1800, 3))
t_rows2 A01 A02 A03 B C10-C12 C13-C15 C16 C17 C18 C19 C20 C21 C22 C23 C24 C25 C26 C27 C28 C29 C30 C31_C32 C33 D35 E36 E37-E39 F G45 G46 G47 H49 H50 H51 H52 H53 I J58 J59_J60 J61 J62_J63 K64 K65 K66 L68A L68B M69_M70 M71 M72 M73 M74_M75 N77 N78 N79 N80-N82 O84 P85 Q86 Q87_Q88 R90-R92 R93 S94 S95 S96 T U TOTAL P3_S14 P3_S15 P3_S13 P3 P51 P52 P52_P53 P53 P5 P6_S21 P6_S22 P6_S2111 P6_S2112 P6 TU TFINU
CPA_A01 3255373.328 164159.16873 1.385300e+04 53.717855 5441101.1 19279.9228 91081.675 1.378855e+04 7586.8584 19.6244076 129235.197 102959.265 98054.286 1052.04014 24.4553086 102.429970 5.0644598 31.6875587 254.860573 25.81661 8.926482e+03 108738.173 88.4475161 4675.3860 619.74164 18949.2023 15550.836 9.914076e+04 1203832.689 10652.04593 9555.1789 18442.2002 9.6034550 193156.677 8e-07 517048.406 28.5210733 21.956794 52.609391 95.5091023 5.7577811 7.1687085 85.1286488 8e-07 5398.5758 4953.9852 1139.3870483 622.3524113 5.3487971 7693.777713 7657.5633 181.655588 1975.165088 126322.36394 1851.2997 9718.02487 8823.8847065 1.179198e+04 907.50078 173.500305 249.126486 2613.6402102 1838.10302 31.2410976 0 11741673.0 6697173.7 164.0053 0 6697337.7 1810266 32522.383 32522.383 0 1842788.725 592774.4 614090.2 530243.2 62531.189 1206864.6 21488664.0 9746991.1
CPA_A02 17994.026 318872.42335 4.000000e-07 57.850432 115596.0 139589.6018 214040.007 1.305188e+05 36887.9385 12.2122831 9275.521 1191.283 5985.494 4415.39956 21.2051446 1945.705819 0.0747864 266.2140704 916.314988 212.43007 1.782254e+04 11922.477 2043.0122830 8618.2589 133.04954 2212.4011 22026.543 2.605285e+00 12056.636 15.42522 5474.8133 368.8287 3.5247661 26345.042 9e-07 2435.782 0.1501359 1.152266 2.081892 4.1868086 0.4251631 18.8086936 0.0674331 9e-07 604.9072 316.2446 0.0366175 333.8980251 19.3270679 494.249140 2831.4327 66.945479 8.262815 587.04223 3745.7582 52.18606 0.2967492 4.375200e-03 13.18401 4.676888 28.521922 27.1667547 108.66050 2.2790325 0 1118551.4 493822.5 0.0000 0 493822.5 0 3282.145 3282.145 0 3282.145 372371.4 30398.3 323578.3 48793.101 402769.7 2018425.8 899874.4
CPA_A03 4392.376 12.27939 7.118052e+04 1.546545 200253.5 53.6427 3456.177 4.621456e-01 289.6989 0.0000019 3321.346 3857.872 3767.250 73.88076 0.0000003 3.302953 0.0000003 0.5891059 9.723433 15.54688 8.000000e-07 4293.601 0.2080047 147.1974 59.47997 33.2291 3088.596 7.319090e-02 3191.919 325.12801 363.9596 647.9323 0.0000067 7020.837 7e-07 101008.384 3.5667967 6.852349 1.733379 0.6590223 0.2380679 0.2696811 3.5386038 7e-07 387.1509 177.1533 1.0916696 0.0000004 0.0000006 4.562843 260.7436 9.742783 113.710127 18.34128 149.5169 1240.93687 93.6393983 3.792443e+02 77.31648 25.918748 1.486315 0.0082631 92.18384 0.0003261 0 413919.9 207597.9 0.0000 0 207597.9 0 1744.768 1744.768 0 1744.768 112362.3 152802.5 112035.0 327.319 265164.9 888427.4 474507.5

Preapring the matrixes for analytical work. By default, the totalling columns are removed from the matrixes. You can leave them for presentation purposes, but for further analytical work they need to be removed.

require(iotables); require (knitr)
hr_use_1800 <- use_table_get ( source = "croatia_2010_1800", geo = "HR",
                               year = 2010, unit = "T_NAC", 
                               labelling = "iotables", 
                               keep_total = FALSE)
output_vector_hr <- output_get(source = "croatia_2010_1800", geo = "HR",
                               year = 2010, unit = "T_NAC", 
                               labelling = "iotables", 
                               keep_total = FALSE)
knitr::kable(head(hr_use_1800, 3))
iotables_row agriculture forestry fishing mining food_beverages_tobacco textiles_apparel wood_products paper_products printing_recording coke_refined_petrol chemical basic_pharmaceutical rubber_plasic mineral_products basic_metals fabricated_metal computer_electronic_optical electrical_equipment machinery motor_vechicles other_transport_equipment furniture repair_machinery eletricity_gas_steam water_services sewage construction trade_motor_vechicles wholesale_trade retail_trade land_transport water_transport air_transport warehousing post_courier accommodation_food publishing audiovisual telecommunications computer_programing_consulting financial_services insurance auxiliary_financial_services real_estate_imputed_a real_estate _services_b legal_accounting_consulting architectural_engineering research_development advertising_marketing other_professional_services rental_leasing employment_services travel_agency_services security_investigation public_administration education human_health residential_care creative_industries sport_recreation membership_organizations repair_computer_durables other_personal_services household_services extraterriorial_organizations
agriculture 3255373.328 164159.16873 1.385300e+04 53.717855 5441101.1 19279.9228 91081.675 1.378855e+04 7586.8584 19.6244076 129235.197 102959.265 98054.286 1052.04014 24.4553086 102.429970 5.0644598 31.6875587 254.860573 25.81661 8.926482e+03 108738.173 88.4475161 4675.3860 619.74164 18949.2023 15550.836 9.914076e+04 1203832.689 10652.04593 9555.1789 18442.2002 9.6034550 193156.677 8e-07 517048.406 28.5210733 21.956794 52.609391 95.5091023 5.7577811 7.1687085 85.1286488 8e-07 5398.5758 4953.9852 1139.3870483 622.3524113 5.3487971 7693.777713 7657.5633 181.655588 1975.165088 126322.36394 1851.2997 9718.02487 8823.8847065 1.179198e+04 907.50078 173.500305 249.126486 2613.6402102 1838.10302 31.2410976 0
forestry 17994.026 318872.42335 4.000000e-07 57.850432 115596.0 139589.6018 214040.007 1.305188e+05 36887.9385 12.2122831 9275.521 1191.283 5985.494 4415.39956 21.2051446 1945.705819 0.0747864 266.2140704 916.314988 212.43007 1.782254e+04 11922.477 2043.0122830 8618.2589 133.04954 2212.4011 22026.543 2.605285e+00 12056.636 15.42522 5474.8133 368.8287 3.5247661 26345.042 9e-07 2435.782 0.1501359 1.152266 2.081892 4.1868086 0.4251631 18.8086936 0.0674331 9e-07 604.9072 316.2446 0.0366175 333.8980251 19.3270679 494.249140 2831.4327 66.945479 8.262815 587.04223 3745.7582 52.18606 0.2967492 4.375200e-03 13.18401 4.676888 28.521922 27.1667547 108.66050 2.2790325 0
fishing 4392.376 12.27939 7.118052e+04 1.546545 200253.5 53.6427 3456.177 4.621456e-01 289.6989 0.0000019 3321.346 3857.872 3767.250 73.88076 0.0000003 3.302953 0.0000003 0.5891059 9.723433 15.54688 8.000000e-07 4293.601 0.2080047 147.1974 59.47997 33.2291 3088.596 7.319090e-02 3191.919 325.12801 363.9596 647.9323 0.0000067 7020.837 7e-07 101008.384 3.5667967 6.852349 1.733379 0.6590223 0.2380679 0.2696811 3.5386038 7e-07 387.1509 177.1533 1.0916696 0.0000004 0.0000006 4.562843 260.7436 9.742783 113.710127 18.34128 149.5169 1240.93687 93.6393983 3.792443e+02 77.31648 25.918748 1.486315 0.0082631 92.18384 0.0003261 0

As a first analytical step we create the input coefficient matrix which is the input of the Leontieff-matrix and the Leontieff-inverse. In this case the original table created by DZS contains an empty column / row pair for “extraterriorial_organizations” which causes an error in the inversion of the Leontieff-matrix. We carefully remove both the column and the row to maintain the symmetry of the matrix and create a meaningful Leontieff-inverse. Keep in mind that for any further additional data you must remove “extraterriorial_organizations” organizations.

require(iotables); require (knitr)
hr_coeff <- input_coefficient_matrix_create( 
  input_flow = hr_use_1800,
  output = output_vector_hr, 
  digits = 6)

#In case we used 'short' type labels:
#hr_coeff <- hr_coeff %>%
#  select ( -U ) %>%
#  filter ( t_rows2 != "CPA_U")

#In case we used 'iotables' type labels:
hr_coeff <- hr_coeff %>%
  select ( -extraterriorial_organizations ) %>%
  filter ( iotables_row != "extraterriorial_organizations")

L_hr <- leontieff_matrix_create( technology_coefficients_matrix =
                                hr_coeff )
I_hr <- leontieff_inverse_create(L_hr)
knitr::kable(head(hr_coeff, 3))
iotables_row agriculture forestry fishing mining food_beverages_tobacco textiles_apparel wood_products paper_products printing_recording coke_refined_petrol chemical basic_pharmaceutical rubber_plasic mineral_products basic_metals fabricated_metal computer_electronic_optical electrical_equipment machinery motor_vechicles other_transport_equipment furniture repair_machinery eletricity_gas_steam water_services sewage construction trade_motor_vechicles wholesale_trade retail_trade land_transport water_transport air_transport warehousing post_courier accommodation_food publishing audiovisual telecommunications computer_programing_consulting financial_services insurance auxiliary_financial_services real_estate_imputed_a real_estate _services_b legal_accounting_consulting architectural_engineering research_development advertising_marketing other_professional_services rental_leasing employment_services travel_agency_services security_investigation public_administration education human_health residential_care creative_industries sport_recreation membership_organizations repair_computer_durables other_personal_services household_services
agriculture 0.151493 0.081330 0.015593 9e-06 0.166346 0.002614 0.036061 0.004177 0.002278 1e-06 0.021075 0.027842 0.035439 2.1e-04 1.1e-05 0.000019 3e-06 5.0e-06 0.000076 2.2e-05 0.001244 0.031846 0.000011 0.000405 2.0e-04 0.003361 0.000321 0.01166 0.035380 4.6e-04 0.000634 0.002843 4e-06 0.017737 0 0.017326 1.5e-05 6e-06 5e-06 1.1e-05 0 1e-06 2.6e-04 0 0.000657 0.000752 0.000117 0.000311 2e-06 0.001898 0.001734 0.000105 0.000444 0.016251 0.000055 0.000678 0.000485 0.005080 0.000183 5.3e-05 0.000188 0.002590 0.000476 8e-05
forestry 0.000837 0.157981 0.000000 9e-06 0.003534 0.018923 0.084742 0.039541 0.011074 1e-06 0.001513 0.000322 0.002163 8.8e-04 1.0e-05 0.000355 0e+00 3.9e-05 0.000273 1.8e-04 0.002485 0.003492 0.000264 0.000746 4.3e-05 0.000392 0.000455 0.00000 0.000354 1.0e-06 0.000364 0.000057 1e-06 0.002419 0 0.000082 0.0e+00 0e+00 0e+00 0.0e+00 0 4e-06 0.0e+00 0 0.000074 0.000048 0.000000 0.000167 6e-06 0.000122 0.000641 0.000039 0.000002 0.000076 0.000111 0.000004 0.000000 0.000000 0.000003 1.0e-06 0.000022 0.000027 0.000028 6e-06
fishing 0.000204 0.000006 0.080120 0e+00 0.006122 0.000007 0.001368 0.000000 0.000087 0e+00 0.000542 0.001043 0.001362 1.5e-05 0.0e+00 0.000001 0e+00 0.0e+00 0.000003 1.3e-05 0.000000 0.001257 0.000000 0.000013 1.9e-05 0.000006 0.000064 0.00000 0.000094 1.4e-05 0.000024 0.000100 0e+00 0.000645 0 0.003385 2.0e-06 2e-06 0e+00 0.0e+00 0 0e+00 1.1e-05 0 0.000047 0.000027 0.000000 0.000000 0e+00 0.000001 0.000059 0.000006 0.000026 0.000002 0.000004 0.000087 0.000005 0.000163 0.000016 8.0e-06 0.000001 0.000000 0.000024 0e+00

You cannot rely on the input_indicator_create function because the official Croatian input-output tables do not include employment data. Instead we created the following table from the official Croatian employment data. The creation of such auxilliary tables may be needed in other analysis, and it is quiet straightforward but requires judgement and manual work.

You need to aggregate the data, in this case the employment data, exactly into the industry / product groups of the IO table. In this lucky case, the employment data has a higher resolution, and we need to add various industry / product groups togeher. For example, the CPA_F industry group (construction) is given for three subindustries in the employment statistics of Croatia that we had to simply add together.

If you have less detailed data, then you have to disaggregate them to higher resolution, which is often not possible, or results in very crude estimates. Eventually, you have to make sure that your labelling of the industries is consistent with the Eurostat metadata vocabulary (“short codes”) or the iotables metadata vocabulary, i.e. you call the construction industry row CPA_F or construction.

kable (head(iotables::croatia_employment_2013, 5))
code iotables_row employment
CPA_A01 agriculture 13001
CPA_A02 forestry 8172
CPA_A03 fishing 1990
CPA_B mining 5356
CPA_C10-C12 food_beverages_tobacco 41463

In case you use the Eurostat metadata vocabulary, your product / industry grouping variable may be called t_row2, or induse, for example. Make sure that you use the same variable name that is name of the key column in your use table. In this case it is .

Remember that we need to remove all totalling rows/columns and the extraterritorial organizations. Because we need a wide-format vector, and the employment data has only one dimension, you need to remove them only once: from the rows in case of the original table or from the columns if it is already spread out to wide form.

You need to add a matching key column in the beginning of the wide-form vector.

multiplier_name <- data.frame ( 
  iotables_row = "employment", stringsAsFactors = FALSE)

hr_emp <- croatia_employment_2013 %>%
  dplyr::select ( iotables_row, employment ) %>%
  tidyr::spread ( iotables_row, employment, !!2:ncol(.) )  %>%
  dplyr::select ( -total, -extraterriorial_organizations ) %>%  
  cbind(data.frame ( 
  iotables_row = "employment", stringsAsFactors = FALSE), .) 

kable (hr_emp[,1:5])
iotables_row accommodation_food advertising_marketing agriculture air_transport
employment 50707 5292 13001 1069
##Only the first 5 columns are shown to preserve space.

Now we can use the iotables functions to get the employment multiplier.

hr_emp_indicator <- input_indicator_create (
  input_matrix = hr_emp, 
  output_vector = select (output_vector_hr, -extraterriorial_organizations  )                        )

employment_multipliers_hr <- multiplier_create ( 
  input_vector    = hr_emp_indicator,
  Im              = I_hr, 
  multiplier_name = "employment_multiplier",
  digits          = 4 ) %>%
  tidyr::gather ( t_cols2, values, !! 2:ncol(.)) %>%
  mutate ( values = values * 1000 )%>%
  arrange (., desc(values))

kable(head(employment_multipliers_hr,23))
iotables_row t_cols2 values
employment_multiplier auxiliary_financial_services 16.3
employment_multiplier education 16.1
employment_multiplier residential_care 8.4
employment_multiplier public_administration 7.4
employment_multiplier membership_organizations 7.4
employment_multiplier post_courier 6.9
employment_multiplier wood_products 6.1
employment_multiplier fabricated_metal 5.8
employment_multiplier forestry 5.4
employment_multiplier retail_trade 5.3
employment_multiplier textiles_apparel 4.8
employment_multiplier machinery 4.6
employment_multiplier human_health 4.6
employment_multiplier publishing 4.4
employment_multiplier furniture 4.3
employment_multiplier water_services 3.9
employment_multiplier security_investigation 3.9
employment_multiplier rubber_plasic 3.8
employment_multiplier computer_electronic_optical 3.8
employment_multiplier creative_industries 3.8
employment_multiplier eletricity_gas_steam 3.7
employment_multiplier research_development 3.7
employment_multiplier employment_services 3.6
##Only the first 5 columns are shown to preserve space.