econioread

econioread provides a step-by-step interface for reading input-output tables from csv, xls, and xlsx files into econio input-output table objects. It handles the messy parts of importing published input-output tables: locating and importing raw spreadsheet cells, building headers, and identifying regions, sector names, and sector types, for both regional and multiregional tables.

Installation

You can install the development version of econioread from GitHub with:

# install.packages("pak")
# pak::pak("UchidaMizuki/econioread")
install.packages("econioread", repos = c('https://uchidamizuki.r-universe.dev', 'https://cloud.r-project.org'))

Example

io_table_reader() builds a step-by-step reader for a single input-output table file. Each io_table_read_*() step is applied in turn with the pipe, and end_step() finishes the pipeline and returns the econio input-output table.

The example below reads a typical published table whose first row is a title, whose first two header rows and columns hold sector codes and sector names, and whose sector blocks end with “total” rows and columns such as Total intermediate inputs and Total output:

library(econioread)

io_table_reader("input-output-table.xlsx") |>
  io_table_read_cells(
    sheets = 1,
    rows_exclude = 1
  ) |>
  io_table_read_headers(
    input_names = c("input_sector_code", "input_sector_name"),
    output_names = c("output_sector_code", "output_sector_name")
  ) |>
  io_table_read_sector_names(
    input_sector_name_glue = "{input_sector_code}_{input_sector_name}",
    output_sector_name_glue = "{output_sector_code}_{output_sector_name}"
  ) |>
  io_table_read_sector_types(
    import_type = "competitive_import",
    industry_total_pattern = "Total intermediate",
    value_added_total_pattern = "Total value added",
    final_demand_total_pattern = "Total final demand",
    export_pattern = "Exports",
    export_total_pattern = "Total exports",
    import_pattern = "\\(less\\) Imports",
    import_total_pattern = "\\(less\\) Total imports",
    total_pattern = "Total output"
  ) |>
  io_table_read_data(value_scale = 1e6) |>
  end_step()

The steps work as follows:

• io_table_read_cells() imports the raw cells of the file. rows_range/cols_range and rows_exclude/cols_exclude trim rows and columns, such as titles and notes, that are not part of the table.
• io_table_read_headers() peels off header rows and columns into named columns. Input headers are taken from the "left" direction and output headers from "up" by default; other unpivotr directions can be given as names, e.g. c(up = "input_sector_code").
• io_table_read_sector_names() combines header columns into a single sector name with glue patterns.
• io_table_read_sector_types() classifies sectors into industry, value-added, final-demand, export, import, and total sectors. *_total_pattern arguments locate the “total” row or column of each block and classify the sectors before it, while *_pattern regular expressions match sector names directly. import_type (either "competitive_import" or "noncompetitive_import") controls which sector types appear on the input and output axes.
• io_table_read_data() parses the cell values, applies value_scale, and builds the input-output table with econio::io_table_regional() or econio::io_table_multiregional(). total_tolerance and check_axes = FALSE accommodate published tables whose subtotals contain rounding drift.
• end_step() returns the finished table.

Custom steps

Published tables often need ad hoc cleaning between steps. Wrap any data-frame function with as_step() (re-exported from the adverbial package) to insert it into the pipeline:

io_table_reader("iotable.xls") |>
  io_table_read_cells(sheets = 1) |>
  io_table_read_headers(
    input_names = c("input_sector_code", "input_sector_name"),
    output_names = c("output_sector_code", "output_sector_name")
  ) |>
  as_step(dplyr::mutate)(
    dplyr::across(
      c(input_sector_code, output_sector_code),
      \(x) stringr::str_remove(x, "^[:upper:]")
    )
  ) |>
  io_table_read_sector_names(
    input_sector_name_glue = "{input_sector_code}_{input_sector_name}",
    output_sector_name_glue = "{output_sector_code}_{output_sector_name}"
  ) |>
  as_step(dplyr::filter)(
    !stringr::str_detect(input_sector_name, "Subtotal$"),
    !stringr::str_detect(output_sector_name, "Subtotal$")
  ) |>
  io_table_read_sector_types(...) |>
  io_table_read_data(value_scale = 1e6) |>
  end_step()

Multiregional tables

For multiregional tables, pass region_type = "multiregional" to io_table_reader(), which adds an io_table_read_regions() step to extract input and output regions before the sector names and types are identified. In a multiregional table, the headers carry region codes and names in addition to sector codes and names:

io_table_reader(
  "multiregional-input-output-table.xlsx",
  region_type = "multiregional"
) |>
  io_table_read_cells(
    sheets = 1,
    rows_exclude = 1:4
  ) |>
  io_table_read_headers(
    input_names = c(
      "input_region_code",
      "input_region_name",
      "input_sector_code",
      "input_sector_name"
    ),
    output_names = c(
      "output_region_code",
      "output_region_name",
      "output_sector_code",
      "output_sector_name"
    )
  ) |>
  io_table_read_regions(
    input_region_glue = "{input_region_code}_{input_region_name}",
    output_region_glue = "{output_region_code}_{output_region_name}"
  ) |>
  io_table_read_sector_names(
    input_sector_name_glue = "{input_sector_code}_{input_sector_name}",
    output_sector_name_glue = "{output_sector_code}_{output_sector_name}"
  ) |>
  io_table_read_sector_types(
    import_type = "competitive_import",
    industry_total_pattern = "Total intermediate",
    value_added_total_pattern = "Total value added",
    final_demand_total_pattern = "Total final demand",
    export_pattern = "Exports",
    import_pattern = "\\(less\\) Imports",
    total_pattern = "Total output"
  ) |>
  io_table_read_data(value_scale = 1e6) |>
  end_step()