qol (version 1.2.1)

any_table: 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 = "sum",
  pct_group = c(),
  pct_value = list(),
  formats = list(),
  by = c(),
  weight = NULL,
  order_by = "stats",
  titles = .qol_options[["titles"]],
  footnotes = .qol_options[["footnotes"]],
  var_labels = .qol_options[["var_labels"]],
  stat_labels = .qol_options[["stat_labels"]],
  box = "",
  workbook = NULL,
  style = .qol_options[["excel_style"]],
  output = .qol_options[["output"]],
  na.rm = .qol_options[["na.rm"]],
  print_miss = .qol_options[["print_miss"]],
  print = .qol_options[["print"]],
  monitor = .qol_options[["monitor"]]
)

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.

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.

na.rm

FALSE by default. If TRUE removes all NA values from the variables.

print_miss

FALSE by default. If TRUE outputs all possible categories of the grouping variables based on the provided formats, even if there are no observations for a combination.

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.

See Also

Creating a custom table style: excel_output_style(), modify_output_style(), number_format_style(), modify_number_formats().

Global style options: set_style_options(), set_variable_labels(), set_stat_labels().

Creating formats: discrete_format() and interval_format().

Functions that can handle formats and styles: frequencies(), crosstabs().

Additional functions that can handle styles: export_with_style()

Additional functions that can handle formats: summarise_plus(), recode(), recode_multi(), transpose_plus(), sort_plus()

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")

state. <- discrete_format(
    "Germany"                       = 1:16,
    "Schleswig-Holstein"            = 1,
    "Hamburg"                       = 2,
    "Lower Saxony"                  = 3,
    "Bremen"                        = 4,
    "North Rhine-Westphalia"        = 5,
    "Hesse"                         = 6,
    "Rhineland-Palatinate"          = 7,
    "Baden-Württemberg"             = 8,
    "Bavaria"                       = 9,
    "Saarland"                      = 10,
    "West"                          = 1:10,
    "Berlin"                        = 11,
    "Brandenburg"                   = 12,
    "Mecklenburg-Western Pomerania" = 13,
    "Saxony"                        = 14,
    "Saxony-Anhalt"                 = 15,
    "Thuringia"                     = 16,
    "East"                          = 11:16)

# Define style
set_style_options(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.
set_titles("This is title number 1 link: https://cran.r-project.org/",
           "This is title number 2",
           "This is title number 3")

set_footnotes("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.),
                     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.),
                     na.rm      = TRUE)

# Percentages based on value variables instead of categories
my_data |> any_table(rows       = c("age + year"),
                     columns    = "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.),
                     na.rm      = TRUE)

# Customize the visual appearance by adding variable and statistic labels. Both
# can also be set as a global option, if labels should be reused over multiple
# tables.
# 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     = "sex",
                     values      = weight,
                     statistics  = c("sum", "pct_group"),
                     order_by    = "interleaved",
                     formats     = list(sex = sex., age = age.),
                     var_labels  = list(age = "Age categories",
                                        sex = "", weight = ""),
                     stat_labels = list(pct = "%"),
                     na.rm       = TRUE)

# Individual styling can also be passed directly
my_style <- excel_output_style(header_back_color = "0077B6",
                               font              = "Times New Roman")

my_data |> any_table(rows       = c("age + year"),
                     columns    = "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       = "age",
                     columns    = "sex",
                     values     = weight,
                     statistics = "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       = "education",
                     columns    = "year",
                     values     = weight,
                     statistics = "pct_group",
                     formats    = list(education = education.),
                     style      = my_style,
                     na.rm      = TRUE)

# The result list from above also carries the transformed data frame if
# needed for further usage
any_table_df <- result_list[["table"]]

# 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    = "education + year",
                     values     = weight,
                     by         = state,
                     statistics = c("sum", "pct_group"),
                     pct_group  = "education",
                     formats    = list(sex = sex., age = age., state = state.,
                                       education = education.),
                     na.rm      = TRUE)

# To save a table as xlsx file you have to set the path and filename in the
# style element
# Example files paths
table_file <- tempfile(fileext = ".xlsx")

# Note: Normally you would directly input the path ("C:/MyPath/") and name ("MyFile.xlsx").
set_style_options(save_path  = dirname(table_file),
                  file       = basename(table_file),
                  sheet_name = "MyTable")

my_data |> any_table(rows       = "sex",
                     columns    = "year",
                     values     = weight,
                     formats    = list(sex = sex.))

# Manual cleanup for example
unlink(table_file)

# Global options are permanently active until the current R session is closed.
# There are also functions to reset the values manually.
reset_style_options()
reset_qol_options()
close_file()