,
Universidad de Granada [cph]| Title: | Obtaining a Flat Table from Pivot Tables |
|---|---|
| Description: | Transformations that allow obtaining a flat table from reports in text or Excel format that contain data in the form of pivot tables. They can be defined for a single report and applied to a set of reports. |
| Authors: | Jose Samos [aut, cre] ,
Universidad de Granada [cph] |
| Maintainer: | Jose Samos <[email protected]> |
| License: | MIT + file LICENSE |
| Version: | 2.1.2.9000 |
| Built: | 2025-01-26 02:39:46 UTC |
| Source: | https://github.com/josesamos/flattabler |
A pivot table should only contain label rows and columns, and an array of values, usually numeric data. This function defines the quantity of rows and columns that contain labels.
define_labels(pt, n_col, n_row) ## S3 method for class 'pivot_table' define_labels(pt, n_col, n_row)define_labels(pt, n_col, n_row) ## S3 method for class 'pivot_table' define_labels(pt, n_col, n_row)
pt |
A |
n_col |
A number, quantity of columns containing pivot table labels. |
n_row |
A number, quantity of rows containing pivot table labels. |
A pivot_table object.
Other pivot table definition functions:
get_page(),
pivot_table(),
set_page()
pt <- pt_ex |> define_labels(n_col = 2, n_row = 2)pt <- pt_ex |> define_labels(n_col = 2, n_row = 2)
Pivot table in data frame with with thousands indicator and decimal numbers.
df_exdf_ex
A data frame.
Other pivot table in data frame:
df_ex_compact,
df_pivottabler
Pivot table in data frame in compact table format: with a column with data from two label fields.
df_ex_compactdf_ex_compact
A data frame.
Other pivot table in data frame:
df_ex,
df_pivottabler
A dataset containing number of train passengers, generated with the
pivottabler package. It contains basic and subtotal labels in the same column.
df_pivottablerdf_pivottabler
A data frame.
https://CRAN.R-project.org/package=pivottabler
Other pivot table in data frame:
df_ex,
df_ex_compact
Set of pivot tables placed horizontally on one sheet.
df_set_hdf_set_h
A data frame.
Other pivot table set in data frame:
df_set_h_v,
df_set_v
Example of a set of pivot tables located horizontally and vertically on one sheet.
df_set_h_vdf_set_h_v
A data frame.
Other pivot table set in data frame:
df_set_h,
df_set_v
Set of pivot tables placed vertically on one sheet.
df_set_vdf_set_v
A data frame.
Other pivot table set in data frame:
df_set_h,
df_set_h_v
Divides a table into tables separated by some empty row or column. Returns a
pivot_table object list.
divide(pt) ## S3 method for class 'pivot_table' divide(pt)divide(pt) ## S3 method for class 'pivot_table' divide(pt)
pt |
A |
Sometimes multiple pivot tables are placed in a text document, imported as
one text table. This operation recursively divides the initial table into
tables separated by some empty row or column. Once a division has been made,
it tries to divide each part of the result. An object is generated for each
indivisible pivot table. Returns a list of pivot_table objects.
If individual tables have a header or footer, they should not be separated from the table by empty rows. If they were, objects would be generated from them that must later be removed from the list of objects in the result.
The operation can be applied to tables located horizontally, vertically or in a grid on the initial table. The only requirement to be able to divide it is that there is some empty row or column between them.
A pivot_table list.
Other flat table list functions:
flatten_table_list(),
get_col_values()
pt <- pivot_table(df_set_h_v) lpt <- pt |> divide()pt <- pivot_table(df_set_h_v) lpt <- pt |> divide()
Extract the given set of labels from a table column in compact format to generate a new column in the table.
extract_labels(pt, col, labels) ## S3 method for class 'pivot_table' extract_labels(pt, col = 1, labels = c())extract_labels(pt, col, labels) ## S3 method for class 'pivot_table' extract_labels(pt, col = 1, labels = c())
pt |
A |
col |
A number, column from which labels are extracted. |
labels |
A vector of strings, set of labels to extract. |
Sometimes a table column includes values of multiple label fields, this is generally known as compact table format. Given a column number and a set of labels, it generates a new column with the labels located at the positions they occupied in the original column and removes them from it.
A pivot_table object.
Other pivot table transformation functions:
fill_labels(),
fill_values(),
remove_agg(),
remove_bottom(),
remove_cols(),
remove_empty(),
remove_k(),
remove_left(),
remove_right(),
remove_rows(),
remove_top(),
replace_dec(),
unpivot()
pt <- pivot_table(df_ex_compact) |> extract_labels(col = 1, labels = c("b1", "b2", "b3", "b4", "Total general"))pt <- pivot_table(df_ex_compact) |> extract_labels(col = 1, labels = c("b1", "b2", "b3", "b4", "Total general"))
Fills missing values in row and column labels for a pivot table. By default, columns are filled down and rows are filled right.
fill_labels(pt, down, right) ## S3 method for class 'pivot_table' fill_labels(pt, down = TRUE, right = TRUE)fill_labels(pt, down, right) ## S3 method for class 'pivot_table' fill_labels(pt, down = TRUE, right = TRUE)
pt |
A |
down |
A boolean, fill down. |
right |
A boolean, fill right. |
A pivot table should only contain label rows and columns, and an array of values, usually numeric data. The row and column closest to the data array are not filled (they must have data defined for each cell).
To correctly carry out this operation, the number of rows and columns that contain labels must be defined, and the table must only contain the pivot table rows and columns.
A pivot_table object.
Other pivot table transformation functions:
extract_labels(),
fill_values(),
remove_agg(),
remove_bottom(),
remove_cols(),
remove_empty(),
remove_k(),
remove_left(),
remove_right(),
remove_rows(),
remove_top(),
replace_dec(),
unpivot()
pt <- pt_ex |> remove_top(1) |> define_labels(n_col = 2, n_row = 2) |> fill_labels(down = TRUE, right = TRUE)pt <- pt_ex |> remove_top(1) |> define_labels(n_col = 2, n_row = 2) |> fill_labels(down = TRUE, right = TRUE)
Fills with NA missing values in a pivot table value array.
fill_values(pt) ## S3 method for class 'pivot_table' fill_values(pt)fill_values(pt) ## S3 method for class 'pivot_table' fill_values(pt)
pt |
A |
A pivot table should only contain label rows and columns, and an array of values, usually numeric data.
To correctly carry out this operation, the number of rows and columns that contain labels must be defined, and the table must only contain the pivot table rows and columns.
A pivot_table object.
Other pivot table transformation functions:
extract_labels(),
fill_labels(),
remove_agg(),
remove_bottom(),
remove_cols(),
remove_empty(),
remove_k(),
remove_left(),
remove_right(),
remove_rows(),
remove_top(),
replace_dec(),
unpivot()
pt <- pt_ex |> remove_top(1) |> define_labels(n_col = 2, n_row = 2) |> fill_values()pt <- pt_ex |> remove_top(1) |> define_labels(n_col = 2, n_row = 2) |> fill_values()
pivot_table object list into a flat tableGiven a list of pivot_table objects and a transformation function that
flattens a pivot_table object, transforms each object using the function
and merges the results into a flat table.
flatten_table_list(lpt = list(), FUN)flatten_table_list(lpt = list(), FUN)
lpt |
A list of |
FUN |
A function, transformation function that flattens a |
A tibble, a flat table implemented by a tibble.
Other flat table list functions:
divide(),
get_col_values()
f <- function(pt) { pt |> set_page(1, 1) |> remove_top(1) |> define_labels(n_col = 2, n_row = 2) |> remove_k() |> replace_dec() |> fill_values() |> fill_labels() |> remove_agg() |> unpivot() } pt <- pivot_table(df_set_h_v) lpt <- pt |> divide() ft <- flatten_table_list(lpt, f)f <- function(pt) { pt |> set_page(1, 1) |> remove_top(1) |> define_labels(n_col = 2, n_row = 2) |> remove_k() |> replace_dec() |> fill_values() |> fill_labels() |> remove_agg() |> unpivot() } pt <- pivot_table(df_set_h_v) lpt <- pt |> divide() ft <- flatten_table_list(lpt, f)
Flat table with page from a pivot table with with thousands indicator and decimal numbers.
ft_exft_ex
A tibble object.
Other flat table:
ft_ex_v2,
ft_set
# Defined by: ft_ex <- pivot_table(df_ex) |> set_page(1, 1) |> remove_top(1) |> define_labels(n_col = 2, n_row = 2) |> remove_k() |> replace_dec() |> fill_values() |> fill_labels() |> remove_agg() |> unpivot()# Defined by: ft_ex <- pivot_table(df_ex) |> set_page(1, 1) |> remove_top(1) |> define_labels(n_col = 2, n_row = 2) |> remove_k() |> replace_dec() |> fill_values() |> fill_labels() |> remove_agg() |> unpivot()
Flat table without page from a pivot table with with thousands indicator and decimal numbers.
ft_ex_v2ft_ex_v2
A tibble object.
Other flat table:
ft_ex,
ft_set
# Defined by: ft_ex_v2 <- pivot_table(df_ex) |> set_page(1, 1) |> remove_top(1) |> define_labels(n_col = 2, n_row = 2) |> remove_k() |> replace_dec() |> fill_values() |> fill_labels() |> remove_agg() |> unpivot(include_page = FALSE, na_rm = FALSE)# Defined by: ft_ex_v2 <- pivot_table(df_ex) |> set_page(1, 1) |> remove_top(1) |> define_labels(n_col = 2, n_row = 2) |> remove_k() |> replace_dec() |> fill_values() |> fill_labels() |> remove_agg() |> unpivot(include_page = FALSE, na_rm = FALSE)
Flat table with page from a pivot table with with thousands indicator and decimal numbers.
ft_setft_set
A tibble object.
Other flat table:
ft_ex,
ft_ex_v2
# Defined by: f <- function(pt) { pt |> set_page(1, 1) |> remove_top(1) |> define_labels(n_col = 2, n_row = 2) |> remove_k() |> replace_dec() |> fill_values() |> fill_labels() |> remove_agg() |> unpivot() } pt <- pivot_table(df_set_h_v) lpt <- pt |> divide() ft_set <- flatten_table_list(lpt, f)# Defined by: f <- function(pt) { pt |> set_page(1, 1) |> remove_top(1) |> define_labels(n_col = 2, n_row = 2) |> remove_k() |> replace_dec() |> fill_values() |> fill_labels() |> remove_agg() |> unpivot() } pt <- pivot_table(df_set_h_v) lpt <- pt |> divide() ft_set <- flatten_table_list(lpt, f)
Gets the values of the indicated column of each table in a list of tables, avoiding the rows at the beginning or the end of each table that are indicated.
get_col_values(lpt, col = 1, start_row = 2, rows_left = 0)get_col_values(lpt, col = 1, start_row = 2, rows_left = 0)
lpt |
|
col |
A number, column to consider. |
start_row |
A number, start row in each table. |
rows_left |
A number, rows to ignore at the end of each table. |
Sometimes a column includes values of multiple label fields. To facilitate the study of the labels included in the same column of several tables, this function gets the values of the indicated column in a list of tables.
Data frame with two columns: Labels in the column, and the index of the table in the list of tables from which they come.
Other flat table list functions:
divide(),
flatten_table_list()
pt <- pivot_table(df_set_h_v) lpt <- pt |> divide() df <- get_col_values(lpt, col = 1, start_row = 4) labels <- sort(unique(df$label))pt <- pivot_table(df_set_h_v) lpt <- pt |> divide() df <- get_col_values(lpt, col = 1, start_row = 4) labels <- sort(unique(df$label))
Get the page information associated with the pivot table represented by the object.
get_page(pt) ## S3 method for class 'pivot_table' get_page(pt)get_page(pt) ## S3 method for class 'pivot_table' get_page(pt)
pt |
A |
Each pivot table implements a report. The pivot table page represents the context of that report. It is useful when we work with several pivot tables with the same structure: for example, the page can allow us to differentiate their origin, date or author. This information is often included in the file name, sheet name, or cells attached to the pivot table.
A vector of strings.
Other pivot table definition functions:
define_labels(),
pivot_table(),
set_page()
page <- pt_ex |> get_page()page <- pt_ex |> get_page()
Pivot table result of transforming a data frame in compact table format: with a column with data from two label fields.
pf_ex_compactpf_ex_compact
Apivot_table object.
Other pivot table:
pt_ex
# Defined by: pf_ex_compact <- pivot_table(df_ex_compact) |> extract_labels(col = 1, labels = c("b1", "b2", "b3", "b4", "Total general"))# Defined by: pf_ex_compact <- pivot_table(df_ex_compact) |> extract_labels(col = 1, labels = c("b1", "b2", "b3", "b4", "Total general"))
pivot_table S3 classCreates a pivot_table object from a data frame. Additional information
associated with the pivot table can be indicated. The data frame data is
converted to character data type.
pivot_table( df, page = "", page_row = 0, page_col = 0, n_col_labels = 0, n_row_labels = 0 )pivot_table( df, page = "", page_row = 0, page_col = 0, n_col_labels = 0, n_row_labels = 0 )
df |
A data frame, contains one or more pivot tables. |
page |
A string, additional information associated with the pivot table. |
page_row, page_col
|
A cell (row and column number), page information included in the table. |
n_col_labels |
A number, number of columns containing pivot table labels. |
n_row_labels |
A number, number of rows containing pivot table labels. |
A pivot_table object.
Other pivot table definition functions:
define_labels(),
get_page(),
set_page()
pt <- pivot_table(df_ex) pt <- pivot_table(df_ex, page = "M4") pt <- pivot_table(df_ex, page_row = 1, page_col = 1) pt <- pivot_table(df_ex, page_row = 1, page_col = 1, n_col_labels = 2, n_row_labels = 2)pt <- pivot_table(df_ex) pt <- pivot_table(df_ex, page = "M4") pt <- pivot_table(df_ex, page_row = 1, page_col = 1) pt <- pivot_table(df_ex, page_row = 1, page_col = 1, n_col_labels = 2, n_row_labels = 2)
Pivot table with with thousands indicator and decimal numbers.
pt_expt_ex
A pivot_table object.
Other pivot table:
pf_ex_compact
# Defined by: pt_ex <- pivot_table(df_ex)# Defined by: pt_ex <- pivot_table(df_ex)
Reads sheets from an Excel file and creates a pivot_table object list, one
from each sheet. Each sheet is expected to contain a pivot table. Each line
in a sheet corresponds to a row in a table. The file and sheet names are
included as part of each object attributes.
read_excel_file( file, sheetIndexes = NULL, sheetNames = NULL, define_page = 3, page_sep = ":" )read_excel_file( file, sheetIndexes = NULL, sheetNames = NULL, define_page = 3, page_sep = ":" )
file |
A string, name of an Excel file. |
sheetIndexes |
A vector of numbers, sheet indexes in the workbook. |
sheetNames |
A vector of strings, sheet names. |
define_page |
A integer, 0: no page, 1: file name as page, 2: sheet name as page, 3: file and sheet names as page, separated by the indicated separator. |
page_sep |
A string, separator to form the page value. |
When multiple files or sheets are handled, the file and/or sheet names may
contain information associated with the pivot table, they could be the table
page information. In order not to lose this information, they are always
stored in each pivot_table object.
A pivot_table object list.
Other import functions:
read_excel_folder(),
read_excel_sheet(),
read_text_file(),
read_text_folder()
file <- system.file("extdata", "excel/set_sheets.xlsx", package = "flattabler") lpt <- read_excel_file(file) lpt <- read_excel_file(file, sheetIndexes = 1:4) lpt <- read_excel_file(file, sheetNames = c("M1", "M2", "M3", "M4"))file <- system.file("extdata", "excel/set_sheets.xlsx", package = "flattabler") lpt <- read_excel_file(file) lpt <- read_excel_file(file, sheetIndexes = 1:4) lpt <- read_excel_file(file, sheetNames = c("M1", "M2", "M3", "M4"))
Reads one sheet (or all sheets) from each of the Excel files in a folder and
creates a list of pivot_table objects, one from each sheet. Each sheet is
expected to contain a pivot table. Each line in a file corresponds to a row
in a table. File and sheet names are included as part of each object
attributes.
read_excel_folder( folder, sheetIndex = 1, sheetName = NULL, allSheets = FALSE, define_page = 3, page_sep = ":" )read_excel_folder( folder, sheetIndex = 1, sheetName = NULL, allSheets = FALSE, define_page = 3, page_sep = ":" )
folder |
A string, folder name. |
sheetIndex |
A number, sheet index in the workbook. |
sheetName |
A string, sheet name. |
allSheets |
A boolean. |
define_page |
A integer, 0: no page, 1: file name as page, 2: sheet name as page, 3: file and sheet names as page, separated by the indicated separator. |
page_sep |
A string, separator to form the page value. |
When multiple files or sheets are handled, the file and/or sheet names may
contain information associated with the pivot table, they could be the table
page information. In order not to lose this information, they are always
stored in each pivot_table object.
A pivot_table object list.
Other import functions:
read_excel_file(),
read_excel_sheet(),
read_text_file(),
read_text_folder()
folder <- system.file("extdata", "excelfolder", package = "flattabler") lpt <- read_excel_folder(folder) lpt <- read_excel_folder(folder, allSheets = TRUE)folder <- system.file("extdata", "excelfolder", package = "flattabler") lpt <- read_excel_folder(folder) lpt <- read_excel_folder(folder, allSheets = TRUE)
Reads an Excel file sheet and creates a pivot_table object. The sheet is
expected to contain one or more pivot tables. Each line in the sheet
corresponds to a row in a table. The file and sheet names can be included as
part of the object attributes.
read_excel_sheet( file, sheetIndex = 1, sheetName = NULL, define_page = 3, page_sep = ":" )read_excel_sheet( file, sheetIndex = 1, sheetName = NULL, define_page = 3, page_sep = ":" )
file |
A string, name of an Excel file. |
sheetIndex |
A number, sheet index in the workbook. |
sheetName |
A string, sheet name. |
define_page |
A integer, 0: no page, 1: file name as page, 2: sheet name as page, 3: file and sheet names as page, separated by the indicated separator. |
page_sep |
A string, separator to form the page value. |
When multiple files or sheets are handled, the file and/or sheet names may
contain information associated with the pivot table, they could be the table
page information. In order not to lose this information, they can be stored
in the pivot_table object.
A pivot_table object.
Other import functions:
read_excel_file(),
read_excel_folder(),
read_text_file(),
read_text_folder()
file <- system.file("extdata", "excelfolder/m4.xlsx", package = "flattabler") pt <- read_excel_sheet(file) pt <- read_excel_sheet(file, sheetName = "Hoja2", define_page = 1)file <- system.file("extdata", "excelfolder/m4.xlsx", package = "flattabler") pt <- read_excel_sheet(file) pt <- read_excel_sheet(file, sheetName = "Hoja2", define_page = 1)
Reads a text file and creates a pivot_table object. The file is expected to
contain one or more pivot tables. Each line in the file corresponds to a row
in a table; within each row, columns are defined by a separator character.
The file name is included as part of the object attributes.
read_text_file(file, sep = ";", encoding = "UTF-8", define_page = TRUE)read_text_file(file, sep = ";", encoding = "UTF-8", define_page = TRUE)
file |
A string, name of a text file. |
sep |
Column separator character. |
encoding |
A string, encoding to be assumed for input strings. |
define_page |
A boolean, include file name as |
When multiple files are handled, the file name may contain information
associated with the pivot table, it could be the table page information. In
order not to lose this information, it can be stored in the pivot_table
object.
A pivot_table object.
Other import functions:
read_excel_file(),
read_excel_folder(),
read_excel_sheet(),
read_text_folder()
file <- system.file("extdata", "m4.csv", package = "flattabler") pt <- read_text_file(file)file <- system.file("extdata", "m4.csv", package = "flattabler") pt <- read_text_file(file)
Reads all text files in a folder and creates a list of pivot_table objects,
one from each file. Each file is expected to contain a pivot table. Each line
in a file corresponds to a row in a table; within each row, columns are
defined by a separator character. File name is included as part of each
object attributes.
read_text_folder(folder, sep = ";", encoding = "UTF-8")read_text_folder(folder, sep = ";", encoding = "UTF-8")
folder |
A string, folder name. |
sep |
Column separator character. |
encoding |
A string, encoding to be assumed for input strings. |
When multiple files are handled, the file name may contain information
associated with the pivot table, it could be the table page information. In
order not to lose this information, it is always stored in each pivot_table
object.
A pivot_table object list.
Other import functions:
read_excel_file(),
read_excel_folder(),
read_excel_sheet(),
read_text_file()
folder <- system.file("extdata", "csvfolder", package = "flattabler") lpt <- read_text_folder(folder)folder <- system.file("extdata", "csvfolder", package = "flattabler") lpt <- read_text_folder(folder)
Aggregated data is recognized because the label of the row or column closest to the array of values is empty.
remove_agg(pt) ## S3 method for class 'pivot_table' remove_agg(pt)remove_agg(pt) ## S3 method for class 'pivot_table' remove_agg(pt)
pt |
A |
A pivot table should only contain label rows and columns, and an array of values, usually numeric data.
Removes pivot table rows and columns that contain aggregated data. It only checks the value in the row or column closest to the array of values.
To correctly carry out this operation, the number of rows and columns that contain labels must be defined, and the table must only contain the pivot table rows and columns.
A pivot_table object.
Other pivot table transformation functions:
extract_labels(),
fill_labels(),
fill_values(),
remove_bottom(),
remove_cols(),
remove_empty(),
remove_k(),
remove_left(),
remove_right(),
remove_rows(),
remove_top(),
replace_dec(),
unpivot()
pt <- pt_ex |> remove_top(1) |> define_labels(n_col = 2, n_row = 2) |> remove_agg()pt <- pt_ex |> remove_top(1) |> define_labels(n_col = 2, n_row = 2) |> remove_agg()
Remove bottom rows from the pivot table represented by the object.
remove_bottom(pt, n) ## S3 method for class 'pivot_table' remove_bottom(pt, n)remove_bottom(pt, n) ## S3 method for class 'pivot_table' remove_bottom(pt, n)
pt |
A |
n |
A number, number of rows to remove. |
A pivot table should only contain label rows and columns, and an array of values, usually numeric data.
All rows not belonging to the pivot table must be removed. It is common to find rows with footer information, which must be removed.
This function is very useful because it is not necessary to know the number of rows in the table.
A pivot_table object.
Other pivot table transformation functions:
extract_labels(),
fill_labels(),
fill_values(),
remove_agg(),
remove_cols(),
remove_empty(),
remove_k(),
remove_left(),
remove_right(),
remove_rows(),
remove_top(),
replace_dec(),
unpivot()
pt <- pt_ex |> remove_bottom(3)pt <- pt_ex |> remove_bottom(3)
Remove the columns whose numbers are indicated from the pivot table represented by the object.
remove_cols(pt, c) ## S3 method for class 'pivot_table' remove_cols(pt, c)remove_cols(pt, c) ## S3 method for class 'pivot_table' remove_cols(pt, c)
pt |
A |
c |
A vector of numbers, column numbers. |
A pivot table should only contain label rows and columns, and an array of values, usually numeric data.
All columns not belonging to the pivot table must be removed.
A pivot_table object.
Other pivot table transformation functions:
extract_labels(),
fill_labels(),
fill_values(),
remove_agg(),
remove_bottom(),
remove_empty(),
remove_k(),
remove_left(),
remove_right(),
remove_rows(),
remove_top(),
replace_dec(),
unpivot()
pt <- pt_ex |> remove_cols(7) pt <- pt_ex |> remove_cols(c(6,7))pt <- pt_ex |> remove_cols(7) pt <- pt_ex |> remove_cols(c(6,7))
Remove rows and columns without data from the pivot table represented by the object.
remove_empty(pt) ## S3 method for class 'pivot_table' remove_empty(pt)remove_empty(pt) ## S3 method for class 'pivot_table' remove_empty(pt)
pt |
A |
A pivot table should only contain label rows and columns, and an array of values, usually numeric data.
All rows and columns not belonging to the pivot table must be removed, including those without data.
A pivot_table object.
Other pivot table transformation functions:
extract_labels(),
fill_labels(),
fill_values(),
remove_agg(),
remove_bottom(),
remove_cols(),
remove_k(),
remove_left(),
remove_right(),
remove_rows(),
remove_top(),
replace_dec(),
unpivot()
df <- df_ex df[seq(from = 1, to = 25, by = 2), ] <- " " df[, seq(from = 1, to = 7, by = 2)] <- " " pt <- pivot_table(df) pt <- pt |> remove_empty()df <- df_ex df[seq(from = 1, to = 25, by = 2), ] <- " " df[, seq(from = 1, to = 7, by = 2)] <- " " pt <- pivot_table(df) pt <- pt |> remove_empty()
A pivot table should only contain label rows and columns, and an array of values, usually numeric data. Values, even though they are numbers, are represented as text and sometimes include a thousands separator that can be removed using this function.
remove_k(pt, sep) ## S3 method for class 'pivot_table' remove_k(pt, sep = ".")remove_k(pt, sep) ## S3 method for class 'pivot_table' remove_k(pt, sep = ".")
pt |
A |
sep |
A character, thousands separator to remove. |
To correctly carry out this operation, the number of rows and columns that contain labels must be defined, and the table must only contain the pivot table rows and columns.
A pivot_table object.
Other pivot table transformation functions:
extract_labels(),
fill_labels(),
fill_values(),
remove_agg(),
remove_bottom(),
remove_cols(),
remove_empty(),
remove_left(),
remove_right(),
remove_rows(),
remove_top(),
replace_dec(),
unpivot()
pt <- pt_ex |> remove_top(1) |> define_labels(n_col = 2, n_row = 2) |> remove_k()pt <- pt_ex |> remove_top(1) |> define_labels(n_col = 2, n_row = 2) |> remove_k()
Remove left columns from the pivot table represented by the object.
remove_left(pt, n) ## S3 method for class 'pivot_table' remove_left(pt, n)remove_left(pt, n) ## S3 method for class 'pivot_table' remove_left(pt, n)
pt |
A |
n |
A number, number of columns to remove. |
A pivot table should only contain label rows and columns, and an array of values, usually numeric data.
All columns not belonging to the pivot table must be removed.
A pivot_table object.
Other pivot table transformation functions:
extract_labels(),
fill_labels(),
fill_values(),
remove_agg(),
remove_bottom(),
remove_cols(),
remove_empty(),
remove_k(),
remove_right(),
remove_rows(),
remove_top(),
replace_dec(),
unpivot()
pt <- pt_ex |> remove_left(3)pt <- pt_ex |> remove_left(3)
Remove right columns from the pivot table represented by the object.
remove_right(pt, n) ## S3 method for class 'pivot_table' remove_right(pt, n)remove_right(pt, n) ## S3 method for class 'pivot_table' remove_right(pt, n)
pt |
A |
n |
A number, number of columns to remove. |
A pivot table should only contain label rows and columns, and an array of values, usually numeric data.
All columns not belonging to the pivot table must be removed.
This function is very useful because it is not necessary to know the number of columns in the table.
A pivot_table object.
Other pivot table transformation functions:
extract_labels(),
fill_labels(),
fill_values(),
remove_agg(),
remove_bottom(),
remove_cols(),
remove_empty(),
remove_k(),
remove_left(),
remove_rows(),
remove_top(),
replace_dec(),
unpivot()
pt <- pt_ex |> remove_right(3)pt <- pt_ex |> remove_right(3)
Remove the rows whose numbers are indicated from the pivot table represented by the object.
remove_rows(pt, r) ## S3 method for class 'pivot_table' remove_rows(pt, r)remove_rows(pt, r) ## S3 method for class 'pivot_table' remove_rows(pt, r)
pt |
A |
r |
A vector of numbers, row numbers. |
A pivot table should only contain label rows and columns, and an array of values, usually numeric data.
All rows not belonging to the pivot table must be removed. It is common to find rows with header or footer information, which must be removed.
A pivot_table object.
Other pivot table transformation functions:
extract_labels(),
fill_labels(),
fill_values(),
remove_agg(),
remove_bottom(),
remove_cols(),
remove_empty(),
remove_k(),
remove_left(),
remove_right(),
remove_top(),
replace_dec(),
unpivot()
pt <- pt_ex |> remove_rows(1) pt <- pt_ex |> remove_rows(c(1, 8, 14, 19, 25, 26))pt <- pt_ex |> remove_rows(1) pt <- pt_ex |> remove_rows(c(1, 8, 14, 19, 25, 26))
Remove top rows from the pivot table represented by the object.
remove_top(pt, n) ## S3 method for class 'pivot_table' remove_top(pt, n)remove_top(pt, n) ## S3 method for class 'pivot_table' remove_top(pt, n)
pt |
A |
n |
A number, number of rows to remove. |
A pivot table should only contain label rows and columns, and an array of values, usually numeric data.
All rows not belonging to the pivot table must be removed. It is common to find rows with header information, which must be removed.
A pivot_table object.
Other pivot table transformation functions:
extract_labels(),
fill_labels(),
fill_values(),
remove_agg(),
remove_bottom(),
remove_cols(),
remove_empty(),
remove_k(),
remove_left(),
remove_right(),
remove_rows(),
replace_dec(),
unpivot()
pt <- pt_ex |> remove_top(3)pt <- pt_ex |> remove_top(3)
A pivot table should only contain label rows and columns, and an array of values, usually numeric data. Values, even though they are numbers, are represented as text and sometimes include a decimal separator different from the one needed; it can be replaced using this function.
replace_dec(pt, sep) ## S3 method for class 'pivot_table' replace_dec(pt, sep = ".")replace_dec(pt, sep) ## S3 method for class 'pivot_table' replace_dec(pt, sep = ".")
pt |
A |
sep |
A character, new decimal separator to use. |
To correctly carry out this operation, the number of rows and columns that contain labels must be defined, and the table must only contain the pivot table rows and columns.
The only decimal separators considered are "." and ",".
A pivot_table object.
Other pivot table transformation functions:
extract_labels(),
fill_labels(),
fill_values(),
remove_agg(),
remove_bottom(),
remove_cols(),
remove_empty(),
remove_k(),
remove_left(),
remove_right(),
remove_rows(),
remove_top(),
unpivot()
pt <- pt_ex |> remove_top(1) |> define_labels(n_col = 2, n_row = 2) |> replace_dec()pt <- pt_ex |> remove_top(1) |> define_labels(n_col = 2, n_row = 2) |> replace_dec()
Define the page information associated with a pivot table. Previously existing information is replaced by new information.
set_page(pt, row, col, page) ## S3 method for class 'pivot_table' set_page(pt, row = 0, col = 0, page = "")set_page(pt, row, col, page) ## S3 method for class 'pivot_table' set_page(pt, row = 0, col = 0, page = "")
pt |
A |
row, col
|
A cell (row and column number), page information included in the table. |
page |
A string, page information. |
Each pivot table implements a report. The pivot table page represents the context of that report. It is useful when we work with several pivot tables with the same structure: for example, the page can allow us to differentiate their origin, date or author. This information is often included in the file name, sheet name, or a cell attached to the pivot table.
A pivot_table object.
Other pivot table definition functions:
define_labels(),
get_page(),
pivot_table()
pt <- pt_ex |> set_page(1, 1) pt <- pt_ex |> set_page(page = "M4")pt <- pt_ex |> set_page(1, 1) pt <- pt_ex |> set_page(page = "M4")
Transforms a pivot table into a flat table (implemented by a tibble). An
additional column with page information can be included. NA values can be
excluded from the array of values.
unpivot(pt, include_page, na_rm, keep_col_names) ## S3 method for class 'pivot_table' unpivot(pt, include_page = TRUE, na_rm = TRUE, keep_col_names = FALSE)unpivot(pt, include_page, na_rm, keep_col_names) ## S3 method for class 'pivot_table' unpivot(pt, include_page = TRUE, na_rm = TRUE, keep_col_names = FALSE)
pt |
A |
include_page |
A boolean, indicates whether a column with the page information is included or not. |
na_rm |
A boolean, indicates whether NA values from the array of values are removed or not. |
keep_col_names |
A boolean, if possible, keep the column names. |
A pivot table should only contain label rows and columns, and an array of values, usually numeric data.
To correctly carry out this operation, the number of rows and columns that contain labels must be defined, and the table must only contain the pivot table rows and columns.
A tibble.
Other pivot table transformation functions:
extract_labels(),
fill_labels(),
fill_values(),
remove_agg(),
remove_bottom(),
remove_cols(),
remove_empty(),
remove_k(),
remove_left(),
remove_right(),
remove_rows(),
remove_top(),
replace_dec()
a_tibble <- pt_ex |> remove_top(1) |> define_labels(n_col = 2, n_row = 2) |> unpivot(include_page = FALSE) a_tibble <- pt_ex |> set_page(1, 1) |> remove_top(1) |> define_labels(n_col = 2, n_row = 2) |> remove_k() |> replace_dec() |> fill_values() |> fill_labels() |> remove_agg() |> unpivot()a_tibble <- pt_ex |> remove_top(1) |> define_labels(n_col = 2, n_row = 2) |> unpivot(include_page = FALSE) a_tibble <- pt_ex |> set_page(1, 1) |> remove_top(1) |> define_labels(n_col = 2, n_row = 2) |> remove_k() |> replace_dec() |> fill_values() |> fill_labels() |> remove_agg() |> unpivot()