United Kingdom Input-Output Analytical Tables

Daniel Antal, based on the work edited by Richard Wild

2024-01-08

The United Kingdom Input-Output Analytical Tables 2010 are used for testing the iotables package, because they are well-documented and detailed, organized data is available with them. The analytical tables are published in an Excel table. A special reader function (which is not exported) was created to read-in all sheets of the Excel table.

It is unlikely that these tables will be required by other purposes than controlling the package. The UK IO tables for other years are available these are consistent with different Blue Books and there could therefore be important methodological and classification changes. Furthermore, ONS currently publishes ESA2010 compatible SIOTs with analytical tables annually.

uk_2010_data <- iotables_download ( source = "uk_2010" )
save ( uk_2010_data, file = file.path('data-raw', 'uk_2010_data.rda'))
uk_test_results <- iotables:::uk_2010_results_get ()
#saved as package data

For the actual symmetric input-output table (see ) you can use the download function originally designed for the Eurostat bulk downloader.

library(iotables)
data(uk_2010_data)
uk_siot              <- iotable_get ( 
                          labelled_io_data = uk_2010_data, 
                          source = "uk_2010_siot" )
uk_published_coeff   <- iotable_get ( 
                          labelled_io_data = uk_2010_data, 
                          source = "uk_2010_coeff" )
uk_published_inverse <- iotable_get ( 
                          labelled_io_data = uk_2010_data, 
                          source = "uk_2010_inverse" )
#> Warning: There was 1 warning in `dplyr::mutate()`.
#> ℹ In argument: `prod_na = forcats::fct_reorder(prod_na,
#>   as.numeric(row_order))`.
#> Caused by warning:
#> ! `fct_reorder()` removing 128 missing values.
#> ℹ Use `.na_rm = TRUE` to silence this message.
#> ℹ Use `.na_rm = FALSE` to preserve NAs.
#> Warning: There was 1 warning in `dplyr::mutate()`.
#> ℹ In argument: `induse = forcats::fct_reorder(induse, as.numeric(col_order))`.
#> Caused by warning:
#> ! `fct_reorder()` removing 128 missing values.
#> ℹ Use `.na_rm = TRUE` to silence this message.
#> ℹ Use `.na_rm = FALSE` to preserve NAs.

Matrix of Coefficients

The matrix of coefficients is described on page 14-15 and the data can be found in the Matrix of coefficients (product by product) sheet of the Excel file.

uk_input_coeff <- input_coefficient_matrix_create(data_table = uk_siot)

coeff_comparison <- select (uk_input_coeff, 1 ) %>%
  left_join ( uk_published_coeff, by = "prod_na")

test_that("correct data is returned", {
  expect_equal(round(uk_input_coeff[,2:8], 8),
               round(coeff_comparison [,2:8], 8)) })
#> Test passed 🥇

Comparing the first 7 columns of the two coefficient matrixes at 8 decimal point precision they are equal. Given the size of the matrixes, not all the matrix is compared here, but such a comparison can be made. However, it is not necessary, because the latter results would show a deviation if parts of the matrix would be different.

Leontief-inverse

The Leontief-inverse is described on pages 16-17 of the article. Again, comparing the first seven numerical columns at 8 digit precision we see that the re-calculated inverse and the published inverse is the same.

uk_calculated_inverse <- leontief_inverse_create(uk_input_coeff)

inverse_comparison <- select (uk_calculated_inverse, 1 ) %>%
  left_join (uk_calculated_inverse, by = "prod_na")

test_that("correct data is returned", {
  expect_equal(round(uk_calculated_inverse[,2:8], 8),
               round(inverse_comparison[,2:8], 8)) })
#> Test passed 🥳

Employment cost effects

employment_effect_results <- uk_test_results %>%
  select ( uk_row_label, `Employment cost effects`)

primary_inputs_uk <- coefficient_matrix_create(
    data_table  = uk_siot, 
    total       = 'output', 
    return_part = 'primary_inputs')

employment_input <- filter (primary_inputs_uk , prod_na == "D1")

employment_effects <- direct_effects_create( employment_input, uk_calculated_inverse ) %>%
  gather ( prod, values, !!2:ncol(.)) %>%
  mutate ( prod_na = prod ) %>%
  select ( -prod ) %>%
  left_join ( select ( metadata_uk_2010, prod_na, uk_row_label  ), 
              by = 'prod_na') %>%
  left_join ( employment_effect_results, by = 'uk_row_label' ) %>%
  filter ( !is.na(uk_row_label )) %>%
  select ( prod_na, uk_row_label, values, `Employment cost effects`)


iotables:::create_knitr_table (
      data_table =  employment_effects[1:10,], 
      digits = 4,
      caption = "Comparison of Calculated And Published Employment Cost Effects", 
      col.names = c("industry code", "row label", "calculated", "published"), 
      col_width = c(2,11,3,3))
Comparison of Calculated And Published Employment Cost Effects
industry code row label calculated published
CPA_01 Products of agriculture, hunting and related services 0,3682 0,3682
CPA_02 Products of forestry, logging and related services 0,4683 0,4683
CPA_03 Fish and other fishing products; aquaculture products; support services to fishing 0,2234 0,2234
CPA_05 Coal and lignite 0,4644 0,4644
CPA_06-07 Crude petroleum and natural gas & Metal ores 0,1657 0,1657
CPA_08 Other mining and quarrying products 0,4305 0,4305
CPA_09 Mining support services 0,1993 0,1993
CPA_10_1 Preserved meat and meat products 0,4776 0,4776
CPA_10_2-3 Processed and preserved fish, crustaceans, molluscs, fruit and vegetables 0,5301 0,5301
CPA_10_4 Vegetable and animal oils and fats 0,3786 0,3786

The comparison of output multipliers show that our code gives the same results. Only ten industries are shown.

GVA effects

GVA is not shown in the Excel table explicitly, but can be calculated as the sum of corporate income (Operating surplus and mixed income), household income (Compensation of employees) and net taxes on production (not products.)

uk_siot2 <- uk_siot %>%
  filter (  prod_na %in% c("B2A3G", "D1", "D29X39") )  %>%
  summarize_if ( is.numeric, sum, na.rm = TRUE ) %>%
  cbind ( data.frame ( prod_na = "GVA"), . ) %>%
  rbind ( uk_siot, .)

gva_effect_results <- uk_test_results %>%
  select ( uk_row_label, `GVA effects`)

gva_input <- coefficient_matrix_create(
   data_table  = uk_siot2, 
   total       = 'output', 
   return_part = 'primary_inputs') %>%
  filter ( prod_na == "GVA" )

gva_effects <- direct_effects_create( gva_input,
                                      uk_calculated_inverse ) %>%
  gather ( prod, values, !!2:ncol(.)) %>%
  mutate ( prod_na = prod ) %>%
  select ( -prod ) %>%
  left_join ( select ( metadata_uk_2010, prod_na, uk_row_label  ), 
              by = 'prod_na') %>%
  left_join ( gva_effect_results, by = 'uk_row_label' ) %>%
  filter ( !is.na(uk_row_label )) %>%
  select ( prod_na, uk_row_label, values, `GVA effects`)

iotables:::create_knitr_table (
      data_table =  gva_effects[1:10,], 
      digits = 4,
      caption = "Comparison of Calculated And Published GVA Effects", 
      col.names = c("industry code", "row label", "calculated", "published"), 
      col_width = c(2,11,3,3))
Comparison of Calculated And Published GVA Effects
industry code row label calculated published
CPA_01 Products of agriculture, hunting and related services 0,6910 0,6910
CPA_02 Products of forestry, logging and related services 0,7472 0,7472
CPA_03 Fish and other fishing products; aquaculture products; support services to fishing 0,7076 0,7076
CPA_05 Coal and lignite 0,6631 0,6631
CPA_06-07 Crude petroleum and natural gas & Metal ores 0,9048 0,9048
CPA_08 Other mining and quarrying products 0,7193 0,7193
CPA_09 Mining support services 0,8963 0,8963
CPA_10_1 Preserved meat and meat products 0,6751 0,6751
CPA_10_2-3 Processed and preserved fish, crustaceans, molluscs, fruit and vegetables 0,7251 0,7251
CPA_10_4 Vegetable and animal oils and fats 0,5628 0,5628

Employment cost multipliers

Turning to multipliers, using the same inputs we get them back with the following code.

empc_multiplier_results <- uk_test_results %>%
  select ( uk_row_label, `Employment cost multiplier`)

empc_indicator_uk <- coefficient_matrix_create(
   data_table  = uk_siot, 
   total       = 'output', 
   return_part = 'primary_inputs') %>%
   filter ( prod_na == 'D1')

empc_multipliers <- input_multipliers_create(
  input_requirements = empc_indicator_uk,
  uk_calculated_inverse) %>%
  gather ( prod, values, !!2:ncol(.)) %>%
  mutate ( prod_na = prod ) %>%
  select ( -prod ) %>%
  left_join ( select ( metadata_uk_2010, prod_na, uk_row_label ), 
              by = 'prod_na') %>%
  left_join ( empc_multiplier_results, by = 'uk_row_label' ) %>%
  filter ( !is.na(uk_row_label )) %>%
  select ( prod_na, uk_row_label, values, `Employment cost multiplier`)

iotables:::create_knitr_table (
      data_table =  empc_multipliers [1:10,], digits = 4,
      caption = "Comparison of Calculated And Published Employment Cost Multipliers", 
      col.names = c("industry code", "row label", "calculated", "published"), 
      col_width = c(2,11,3,3))
Comparison of Calculated And Published Employment Cost Multipliers
industry code row label calculated published
CPA_01 Products of agriculture, hunting and related services 2,1111 2,1111
CPA_02 Products of forestry, logging and related services 2,3892 2,3892
CPA_03 Fish and other fishing products; aquaculture products; support services to fishing 2,7088 2,7088
CPA_05 Coal and lignite 1,6419 1,6419
CPA_06-07 Crude petroleum and natural gas & Metal ores 2,2748 2,2748
CPA_08 Other mining and quarrying products 1,4642 1,4642
CPA_09 Mining support services 3,3424 3,3424
CPA_10_1 Preserved meat and meat products 2,4978 2,4978
CPA_10_2-3 Processed and preserved fish, crustaceans, molluscs, fruit and vegetables 1,8343 1,8343
CPA_10_4 Vegetable and animal oils and fats 1,6153 1,6153

GVA multipliers

Following from the GVA effects, after summarizing GVA and adding it to the input requirements we can calculate the GVA multipliers.

gva_multipliers <- input_multipliers_create(
  input_requirements = gva_input,
                       uk_calculated_inverse) %>%
  gather ( prod, values, !!2:ncol(.)) %>%
  mutate ( prod_na = prod ) %>%
  select ( -prod ) %>%
  left_join ( select ( metadata_uk_2010, prod_na, uk_row_label  ), 
              by = 'prod_na') %>%
  left_join ( gva_multiplier_results, by = 'uk_row_label' ) %>%
  filter ( !is.na(uk_row_label )) %>%
  select ( prod_na, uk_row_label, values, `GVA multiplier`)

iotables:::create_knitr_table (
  data_table =  gva_multipliers [1:10,], digits = 4,
  caption = "Comparison of Calculated And Published GVA Multipliers", 
  col.names = c("industry code", "row label", "calculated", "published"), 
  col_width = c(2,11,3,3))

iotables:::create_knitr_table (
  data_table =  gva_multipliers [1:10,], 
  digits = 4,
  caption = "Comparison of Calculated And Published GVA Multipliers", 
  col.names = c("industry code", "row label", 
                "calculated", "published"), 
  col_width = c(2,11,3,3))

Output multipliers

At last, the comparison of output multipliers show that our code gives the same results. Only ten industries are shown.

output_multiplier_results <- uk_test_results %>%
  select ( uk_row_label, `Output multiplier`)

uk_output_multipliers <- output_multiplier_create(uk_input_coeff) %>%
  gather ( prod, values, !!2:ncol(.)) %>%
  mutate ( prod_na = prod ) %>%
  select ( -prod ) %>%
  left_join ( select ( metadata_uk_2010, prod_na, uk_row_label  ), 
              by = 'prod_na') %>%
  left_join ( output_multiplier_results, 
              by = 'uk_row_label' ) %>%
  filter ( !is.na(uk_row_label) ) %>%
  select ( prod_na, uk_row_label, values, `Output multiplier`)

iotables:::create_knitr_table (
      data_table =  uk_output_multipliers [1:10,], 
      digits = 4,
      caption = "Comparison of Calculated And Published Output Multipliers", 
      col.names = c("industry code", "row label",
                    "calculated", "published"), 
      col_width = c(2,11,3,3))
Comparison of Calculated And Published Output Multipliers
industry code row label calculated published
CPA_01 Products of agriculture, hunting and related services 1,8312 1,8312
CPA_02 Products of forestry, logging and related services 2,1187 2,1187
CPA_03 Fish and other fishing products; aquaculture products; support services to fishing 1,6284 1,6284
CPA_05 Coal and lignite 1,7891 1,7891
CPA_06-07 Crude petroleum and natural gas & Metal ores 1,4155 1,4155
CPA_08 Other mining and quarrying products 1,5231 1,5231
CPA_09 Mining support services 1,5692 1,5692
CPA_10_1 Preserved meat and meat products 2,2693 2,2693
CPA_10_2-3 Processed and preserved fish, crustaceans, molluscs, fruit and vegetables 1,9499 1,9499
CPA_10_4 Vegetable and animal oils and fats 1,6088 1,6088