United Kingdom Input-Output Analytical Tables
Daniel Antal, based on the work edited by Richard Wild
2022-02-10
Source:vignettes/united_kingdom_2010.Rmd
united_kingdom_2010.Rmd
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" )
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))
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 mulitipliers 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))
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))
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 mulitipliers 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))
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 |