Pivot table

A pivot table allows to represent information in a structured way, mainly to be analysed by a person or to make a graphical representation of it. In addition to a header and/or footer, it contains label rows and columns, and a matrix of values, usually numeric data.

The table above, contained in the df_ex variable of the package, has the following parts:

• The header is made up of row r1 and the intersection between rows and columns of labels (cells of c1 and c2 with r2 and r3). Let us suppose that the content of the cell (r1, c1) is especially relevant, it is part of the table header and identifies the content of this pivot table with respect to others: It is the identifier of the page.

• Columns c1 and c2, also rows r2 and r3, contain labels, except those of the intersection that are part of the header. It is common to try not to repeat the values of the outer labels, it being understood that, if there is no value, the value of that position is the last one shown in the same row or column (this is the case in column c1 and in row r2). In the innermost labels, if there are no values, it is because the corresponding position in the outer row or column corresponds to an aggregate (this is the case in column c2 and in row r3).

• The matrix of values is made up of rows and columns after those containing labels (rows from r4 on, and columns from c3 on). Each value of this matrix is characterized by the combination of labels of the corresponding row and column. It is common to find null values in that matrix because the data is not produced or recorded for the combination of labels that define it, that is, the data is usually scattered. In addition to base data, aggregated data can be included in the matrix. Since it was intended for a person, the thousands separator had been used and, both this and the decimal separator, had been used with the Spanish style.

Obtaining a flat table

A flat database or flat-file database is a database that only contains a single table. A flat table is a generally denormalized table that is not related to other tables. It is not necessarily tidy data (in the sense of the tidyverse package) but in each column all the data must be of the same type, so that it can be easily stored in a RDBMS (Relational Database Management System).

A pivot table is not a flat table, but from a pivot table we can obtain a flat table, that is what we are going to do with the help of the flattabler package. Below are the transformations performed using its functions.

library(flattabler)

ft <- pivot_table(df_ex) |>
  set_page(1, 1) |>
  remove_top(1) |>
  define_labels(n_col = 2, n_row = 2) |>
  fill_labels() |>
  remove_agg() |>
  fill_values() |>
  remove_k() |>
  replace_dec() |>
  unpivot()

Starting from the pivot table in the variable df_ex:

1. We get an object using the pivot_table() function, the constructor of the pivot_table class.

2. We define that the value that identifies the pivot table (its page) is in cell (r1, c1), by means of set_page(1, 1).

3. We should leave only the labels and the matrix of values, therefore rows or columns with other information have to be removed. The cells between the rows and columns of labels are ignored (cells of c1 and c2 with r2 and r3). We delete the first row using remove_top(1) because it does not contain labels.

4. Then, we define the number of rows and columns containing labels using define_labels(n_col = 2, n_row = 2). There are two columns and two rows of labels.

5. Since there are more than one row or column with labels, the values of the labels of the first row and column have not been repeated. They are filled using fill_labels().

6. The pivot table contains aggregated data. It is removed by remove_agg(). It is recognized exclusively because there are no values in the row or column of the labels next to the array of values.

7. The array of values has gaps that, instead of having a numeric value, have an empty string. In R it is more appropriate to have NA if the data is not available. This operation is performed through fill_values().

8. The example is a Spanish report that uses thousands and decimal separators in the value matrix. We need to adapt them to the R syntax for numbers. This operation is carried out using remove_k() to remove the thousands separator and replace_dec() to replace the decimal separator.

9. Finally, it is transformed into a flat table by unpivot(): each row corresponds to a value with its combination of labels. By default, NA values are not considered.

The result obtained can be seen in the following table. An additional label has been added with the value that identifies the pivot table, the pivot table page.

Since this table is not intended to be analysed directly by a person, aggregated data has been removed as well as data that was not available for tag combinations (of course this is optional). The numerical data has been transformed so that it can be easily processed in R.

The result of the transformations is a tibble that can be further transformed using the functions of the tidyverse package.

Transforming a set of pivot tables

Once we have defined the necessary transformations for a pivot table, we can apply them to any other with the same structure. Candidate tables can have different number of rows or columns, depending on the number of labels, but they must have the same number of rows and columns of labels, and the same number of header or footer rows, so that the transformations are the same for each table.

To easily perform this operation, we define a function f from the transformations, as shown below.

f <- function(pt) {
  pt |>
  set_page(1, 1) |>
  remove_top(1) |>
  define_labels(n_col = 2, n_row = 2) |>
  fill_labels() |>
  remove_agg() |>
  fill_values() |>
  remove_k() |>
  replace_dec() |>
  unpivot()
}

The only difference from the original transformation is that we don’t need to build a pivot_table object because the input functions provided by the package build it automatically.

The package has functions that allow data to be read in either text format or Excel format, from a single file or from a folder with multiple files. For example, the following code reads files in CSV format contained in a package data folder. The result is a list of pivot_table objects that can be directly transformed.

folder <- system.file("extdata", "csvfolder", package = "flattabler")
lpt <- read_text_folder(folder)

class(lpt[[1]])
#> [1] "pivot_table"

Given a list of pivot tables, lpt, flatten_table_list() applies the transformation defined by function f to each of them, and merges the results into a flat table.

ftl <- flatten_table_list(lpt, f)

In this case, the full result is not shown in this document because it takes up too much space, but a sample is shown below.

Once we have a flat table, implemented using tibble, we can use tidyverse package components to transform it, as shown below. In this case all results are displayed.

t <- ftl |>
  tidyr::pivot_wider(names_from = page, values_from = value) |>
  dplyr::rename(B = col1, A = col2, E = row1, D = row2) |> 
  dplyr::select(A, B, D, E, M1, M2, M3, M4) |> 
  dplyr::arrange(A, B, D, E)

Pivot table import

The objective of import operations is to obtain external data that contains one or more pivot tables to transform them.

Three formats have been considered: text file, Excel file, and data frame.

In the case of working with files, the situation of jointly treating all the files in a folder has also been considered. In the case of Excel, alternatively, all the sheets in a file can be treated together.

The S3 pivot_table class has been defined in the package. Transform operations are defined for objects of this class. Import operations can be classified into two groups: those that return a pivot_table object, and those that return a list of pivot_table objects. Objects in a list can be transformed together.

pt <- pivot_table(df_ex)

pt <- pivot_table(df_ex, page = "M4")

file <- system.file("extdata", "csv/set_v_ie.csv", package = "flattabler")
pt <- read_text_file(file, define_page = TRUE)

file <- system.file("extdata", "excel/set_v.xlsx", package = "flattabler")
pt <- read_excel_sheet(file, define_page = 3)

pt <- pivot_table(df_set_h_v)
lpt <- pt |> divide()

folder <- system.file("extdata", "csvfolder", package = "flattabler")
lpt <- read_text_folder(folder)

folder <- system.file("extdata", "excelfolder", package = "flattabler")
lpt <- read_excel_folder(folder)

file <- system.file("extdata", "excel/set_sheets.xlsx", package = "flattabler")
lpt <- read_excel_file(file)

Pivot table definition

Once we have a pivot_table object or list of objects, pivot tables have to be defined. Each object generated by import operations contains a text table, it is expected to contain a pivot table, but may also have more information, generally in the form of a table header or footer. Through this set of operations we transform the text table in the object into a pivot table and define its characteristics.

A pivot_table object should only contain label rows and columns, and an matrix of values, usually numeric data. Additional information can be used to identify the pivot table relative to other similar tables: can be used to define the pivot table page.

Page: We consider the page of the pivot table as the literal that identifies it with respect to other homogeneous tables; generally it is the value of an attribute (i.e., 2023, 2022,…) or the name of a variable (i.e., amount, profit,…). When multiple pivot tables are integrated into a flat table, the page is essential to distinguish the origin of the data. It is considered as an additional label.

The workflow is generally as follows:

1. Explore the table to determine its distribution and characteristics. If we start from a list of pivot_table objects, we will explore each one of the tables. In order to transform them together, they should have homogeneous structure. We will use member reference (instead of list slicing) to access the objects in the list. Example: pt <- lpt[[1]].

2. In case the text table contains multiple pivot tables, they can be obtained using divide(), which returns a list of pivot_table objects; therefore, we return to the first step.

3. Define the characteristics of the pivot table: Number of rows and columns with labels, and page value.

4. Remove the rows and columns that are not part of the labels or matrix of values: It should only contain the rows and columns of labels and a matrix of values.

Therefore, we still need to review the functions for these last two steps.: Functions to define the pivot table characteristics, and to remove the rows and columns that are not part of it.

pt <- pt |> set_page(1, 1)

pt <- pt |> define_labels(n_col = 2, n_row = 2)

pt <- pt |> remove_top(1)

Pivot table transformation

Once a pivot_table object only contains pivot table data, and its attributes have been defined, it could be transformed into a flat table. However, we can take advantage of the table structure to modify and complete it. Therefore, optionally, we can complete and transform the components of the pivot table: Labels and values.

pt <- pt |> fill_labels()

pt <- pt |> remove_agg()

pt <- pivot_table(df_ex_compact) |>
  extract_labels(col = 1,
                 labels = c("b1", "b2", "b3", "b4", "Total general"))

file <- system.file("extdata", "csv/set_v_compact.csv", package = "flattabler")
pt <- read_text_file(file)
lpt <- pt |> divide()

df <- get_col_values(lpt, start_row = 4)
labels <- sort(unique(df$label))

pt <- pt |> fill_values()

pt <- pt |> remove_k()

pt <- pt |> replace_dec()

Flat table generation

In order to generate a flat table from a pivot_table object, it is an essential requirement to have properly defined its attributes and that it only contains the pivot table label rows and columns, and the matrix of values. Optionally, if the table has a usual structure, we could have transformed the values and labels, if necessary.

We can generate a flat table from a pivot table (a pivot_table object) or from a list of pivot tables (a list of pivot_table objects).

ft <- pivot_table(df_ex) |>
  set_page(1, 1) |>
  remove_top(1) |>
  define_labels(n_col = 2, n_row = 2) |>
  fill_labels() |>
  remove_agg() |>
  fill_values() |>
  remove_k() |>
  replace_dec() |>
  unpivot()

f <- function(pt) {
  pt |>
    set_page(1, 1) |>
    define_labels(n_col = 2, n_row = 2) |>
    remove_top(1) |>
    fill_labels() |>
    remove_agg() |>
    fill_values() |>
    remove_k() |>
    replace_dec() |>
    unpivot()
}

ft <- flatten_table_list(lpt, f)