any_table: Compute Any Possible Table
In qol: Powerful 'SAS' Inspired Concepts for more Efficient Bigger Outputs

any_table

R Documentation

Compute Any Possible Table

Description

any_table() produces any possible descriptive table in 'Excel' format. Any number of variables can be nested and crossed. The output is an individually styled 'Excel' table, which also receives named ranges, making it easier to read the data back in.

Usage

any_table(
  data_frame,
  rows,
  columns = "",
  values,
  statistics = c("sum"),
  pct_group = c(),
  pct_value = list(),
  formats = list(),
  by = c(),
  weight = NULL,
  order_by = "stats",
  titles = c(),
  footnotes = c(),
  var_labels = list(),
  stat_labels = list(),
  box = "",
  workbook = NULL,
  style = excel_output_style(),
  output = "excel",
  pre_summed = FALSE,
  na.rm = FALSE,
  print = TRUE,
  monitor = FALSE
)

Arguments

`data_frame`	A data frame in which are the variables to tabulate.
`rows`	A vector that provides single variables or variable combinations that should appear in the table rows. To nest variables use the form: "var1 + var2 + var3 + ...".
`columns`	A vector that provides single variables or variable combinations that should appear in the table rows. To nest variables use the form: "var1 + var2 + var3 + ...".
`values`	A vector containing all variables that should be summarised.
`statistics`	Available functions: "sum" -> Weighted and unweighted sum "sum_wgt" -> Sum of all weights "freq" -> Unweighted frequency "freq_g0" -> Unweighted frequency of all values greater than zero "pct_group" -> Weighted and unweighted percentages within the respective group "pct_value" -> Weighted and unweighted percentages between value variables "pct_total" -> Weighted and unweighted percentages compared to the grand total "mean" -> Weighted and unweighted mean "median" -> Weighted and unweighted median "mode" -> Weighted and unweighted mode "min" -> Minimum "max" -> Maximum "sd" -> Weighted and unweighted standard deviation "variance" -> Weighted and unweighted standard variance "first" -> First value "last" -> Last value "pn" -> Weighted and unweighted percentiles (any p1, p2, p3, ... possible) "missing" -> Missings generated by the value variables
`pct_group`	If pct_group is specified in the statistics, this option is used to determine which variable of the row and column variables should add up to 100 %. Multiple variables can be specified in a vector to generate multiple group percentages.
`pct_value`	If pct_value is specified in the statistics, you can pass a list here which contains the information for a new variable name and between which of the value variables percentages should be computed.
`formats`	A list in which is specified which formats should be applied to which variables.
`by`	Compute tables stratified by the expressions of the provided variables.
`weight`	Put in a weight variable to compute weighted results.
`order_by`	Determine how the columns will be ordered. "values" orders the results by the order you provide the variables in values. "stats" orders them by the order under statistics. "values_stats" is a combination of both. "columns" keeps the order as given in columns and "interleaved" alternates the stats.
`titles`	Specify one or more table titles.
`footnotes`	Specify one or more table footnotes.
`var_labels`	A list in which is specified which label should be printed for which variable instead of the variable name.
`stat_labels`	A list in which is specified which label should be printed for which statistic instead of the statistic name.
`box`	Provide a text for the upper left box of the table.
`workbook`	Insert a previously created workbook to expand the sheets instead of creating a new file.
`style`	A list of options can be passed to control the appearance of excel outputs. Styles can be created with `excel_output_style()`.
`output`	The following output formats are available: excel and excel_nostyle.
`pre_summed`	FALSE by default. If TRUE this function works with pre summarised data. This can be used, if not all the needed results can be calculated by `any_table()` and need to be prepared in advance. Enabling you to still make use of the styled tabulation. For this to work, the values have to carry the statistic extension (e.g. "_sum", "_pct") in the variable name.
`na.rm`	FALSE by default. If TRUE removes all NA values from the variables.
`print`	TRUE by default. If TRUE prints the output, if FALSE doesn't print anything. Can be used if one only wants to catch the output data frame and workbook with meta information.
`monitor`	FALSE by default. If TRUE, outputs two charts to visualize the functions time consumption.

Details

any_table() is based on the 'SAS' procedure Proc Tabulate, which provides efficient and readable ways to perform complex tabulations.

With this function you can combine any number of variables in any possible way, all at once. You just define which variables or variable combinations should end up in the table rows and columns with a simple syntax. Listing variables in a vector like c("var1", "var2", "var3",...) means to put variables below (in case of the row variables) or besides (in case of the column variables) each other. Nesting variables is as easy as putting a plus sign between them, e.g. c("var1 + var2", "var2" + "var3" + "var4", etc.). And of course you can combine both versions.

The real highlight is, that this function not only creates all the desired variable combinations and exports them to an 'Excel' file, it prints a fully custom styled table to a workbook. Setting up a custom, reusable style is as easy as setting up options like: provide a color for the table header, set the font size for the row header, should borders be drawn for the table cells yes/no, and so on. Merging doubled header texts, happens automatically.

With this function you basically can fully concentrate on designing a table, instead of thinking hard about how to calculate where to put a border or to even manually prepare a designed workbook.

Value

Returns a list with the data table containing the results for the table, the formatted 'Excel' workbook and the meta information needed for styling the final table.

Examples

# Example data frame
my_data <- dummy_data(1000)
my_data[["person"]] <- 1

# Formats
age. <- discrete_format(
    "Total"          = 0:100,
    "under 18"       = 0:17,
    "18 to under 25" = 18:24,
    "25 to under 55" = 25:54,
    "55 to under 65" = 55:64,
    "65 and older"   = 65:100)

sex. <- discrete_format(
    "Total"  = 1:2,
    "Male"   = 1,
    "Female" = 2)

education. <- discrete_format(
    "Total"            = c("low", "middle", "high"),
    "low education"    = "low",
    "middle education" = "middle",
    "high education"   = "high")

# Define style
my_style <- excel_output_style(column_widths = c(2, 15, 15, 15, 9))

# Define titles and footnotes. If you want to add hyperlinks you can do so by
# adding "link:" followed by the hyperlink to the main text.
titles <- c("This is title number 1 link: https://cran.r-project.org/",
            "This is title number 2",
            "This is title number 3")
footnotes <- c("This is footnote number 1",
               "This is footnote number 2",
               "This is footnote number 3 link: https://cran.r-project.org/")

# Output complex tables with different percentages
my_data |> any_table(rows       = c("sex + age", "sex", "age"),
                     columns    = c("year", "education + year"),
                     values     = weight,
                     statistics = c("sum", "pct_group"),
                     pct_group  = c("sex", "age", "education", "year"),
                     formats    = list(sex = sex., age = age.,
                                       education = education.),
                     style      = my_style,
                     na.rm      = TRUE)

# If you want to get a clearer vision of what the result table looks like, in terms
# of the row and column categories, you can write the code like this, to make out
# the variable crossings and see the order.
my_data |> any_table(columns = c(            "year", "education + year"),
                     rows    = c("sex + age",
                                 "sex",
                                 "age"),
                     values     = weight,
                     statistics = c("sum", "pct_group"),
                     pct_group  = c("sex", "age", "education", "year"),
                     formats    = list(sex = sex., age = age.,
                                       education = education.),
                     style      = my_style,
                     na.rm      = TRUE)

# Percentages based on value variables instead of categories
my_data |> any_table(rows       = c("age + year"),
                     columns    = c("sex"),
                     values     = c(probability, person),
                     statistics = c("pct_value", "sum", "freq"),
                     pct_value  = list(rate = "probability / person"),
                     weight     = weight,
                     formats    = list(sex = sex., age = age.),
                     style      = my_style,
                     na.rm      = TRUE)

# Customize the visual appearance by adding titles, footnotes and variable
# and statistic labels.
# Note: You don't have to describe every element. Sometimes a table can be more
# readable with less text. To completely remove a variable label just put in an
# empty text "" as label.
my_data |> any_table(rows        = c("age + year"),
                     columns     = c("sex"),
                     values      = weight,
                     statistics  = c("sum", "pct_group"),
                     order_by    = "interleaved",
                     formats     = list(sex = sex., age = age.),
                     titles      = titles,
                     footnotes   = footnotes,
                     var_labels  = list(age = "Age categories",
                                       sex = "", weight = ""),
                     stat_labels = list(pct = "%"),
                     style       = my_style,
                     na.rm       = TRUE)

# With individual styling
my_style <- my_style |> modify_output_style(header_back_color = "0077B6",
                                            font              = "Times New Roman")

my_data |> any_table(rows       = c("age + year"),
                     columns    = c("sex"),
                     values     = c(probability, person),
                     statistics = c("pct_value", "sum", "freq"),
                     pct_value  = list(rate = "probability / person"),
                     weight     = weight,
                     formats    = list(sex = sex., age = age.),
                     style      = my_style,
                     na.rm      = TRUE)

# Pass on workbook to create more sheets in the same file
my_style <- my_style |> modify_output_style(sheet_name = "age_sex")

result_list <- my_data |>
           any_table(rows       = c("age"),
                     columns    = c("sex"),
                     values     = weight,
                     statistics = c("sum"),
                     formats    = list(sex = sex., age = age.),
                     style      = my_style,
                     na.rm      = TRUE,
                     print      = FALSE)

my_style <- my_style |> modify_output_style(sheet_name = "edu_year")

my_data |> any_table(workbook   = result_list[["workbook"]],
                     rows       = c("education"),
                     columns    = c("year"),
                     values     = weight,
                     statistics = c("pct_group"),
                     formats    = list(education = education.),
                     style      = my_style,
                     na.rm      = TRUE)

# Output multiple complex tables by expressions of another variable.
# If you specify the sheet name as "by" in the output style, the sheet
# names are named by the variable expressions of the by-variable. Otherwise
# the given sheet named gets a running number.
my_style <- my_style |> modify_output_style(sheet_name = "by")

my_data |> any_table(rows       = c("sex", "age"),
                     columns    = c("education + year"),
                     values     = weight,
                     by         = state,
                     statistics = c("sum", "pct_group"),
                     pct_group  = c("education"),
                     formats    = list(sex = sex., age = age., state = state.,
                                       education = education.),
                     titles     = titles,
                     footnotes  = footnotes,
                     style      = my_style,
                     na.rm      = TRUE)

qol documentation built on Dec. 14, 2025, 1:06 a.m.