Contingency Tables to Excel

Warning: xltabr is in early development. Please raise an issue if you find any bugs

Introduction

xltabr allows you to write formatted cross tabulations to Excel using openxlsx. It has been developed to help automate the process of publishing Official Statistics.

The package works best when the input dataframe is the output of a crosstabulation performed by reshape2:dcast. This allows the package to autorecognise various elements of the cross tabulation, which can be styled accordingly.

For example, given a crosstabulation ct produced by reshape2, the following code produces the table shown.

titles = c("Breakdown of car statistics", "Cross tabulation of drive and age against type*")
footers = "*age as of January 2015"
wb <- xltabr::auto_crosstab_to_wb(ct, titles = titles, footers = footers)
openxlsx::openXL(wb)

This readme provides a variety of examples of increasing complexity. It is based on a simulated dataset built into the package, which you can see here.

Getting started

Much of xltabr utility comes from its ability to automatically format cross tabulations which have been produced by reshape2:dcast.

The package provides a core convenience function called xltabr::auto_crosstab_to_xl. This wraps more advanced functionality, at the cost of reducing flexibility.

The following code assumes you've read in the synthetic data as follows:

# Read in data 
path <- system.file("extdata", "synthetic_data.csv", package="xltabr")
df <- read.csv(path, stringsAsFactors = FALSE)

Example 1: Simple cross tabulation to Excel

# Create a cross tabulation using reshape2
ct <- reshape2::dcast(df, drive + age  ~ type, value.var= "value", margins=c("drive", "age"), fun.aggregate = sum)
ct <- dplyr::arrange(ct, -row_number())

# Use the main convenience function from xltabr to output to excel
tab <- xltabr::auto_crosstab_to_wb(ct, return_tab = TRUE)  #wb is an openxlsx workbook object
openxlsx::openXL(tab$wb)

Example 2: Standard data frame to Excel

There is also a convenience function to write a standard data.frame to Excel:

wb <- xltabr::auto_df_to_wb(mtcars)
openxlsx::openXL(wb)

Example 3: Add in titles and footers

titles = c("Breakdown of car statistics", "Cross tabulation of drive and age against type*")
footers = "*age as of January 2015"
wb <- xltabr::auto_crosstab_to_wb(ct, titles = titles, footers = footers)
openxlsx::openXL(wb)

Example 4: Supply custom styles

path <- system.file("extdata", "styles_pub.xlsx", package = "xltabr")
cell_path <- system.file("extdata", "style_to_excel_number_format_alt.csv", package = "xltabr")
xltabr::set_style_path(path)
xltabr::set_cell_format_path(cell_path)
wb <- xltabr::auto_crosstab_to_wb(ct)
openxlsx::openXL(wb)

Example 5: Output more than one table

# Change back to default styles
xltabr::set_style_path()
xltabr::set_cell_format_path()

# Create second crosstab
ct2 <- reshape2::dcast(df, drive + age ~ colour, value.var= "value", margins=c("drive", "age"), fun.aggregate = sum)
ct2 <- dplyr::arrange(ct2, -row_number())

tab <- xltabr::auto_crosstab_to_wb(ct, titles = titles, footers = c(footers, ""), return_tab = TRUE)

titles2 = c("Table 2: More car statistics", "Cross tabulation of drive and age against colour*")
footers2 = "*age as of January 2015"
wb <- xltabr::auto_crosstab_to_wb(ct2, titles = titles2, footers = footers2, insert_below_tab = tab)
openxlsx::openXL(wb)

Example 6: Output more than one table, with different styles

tab <- xltabr::auto_crosstab_to_wb(ct, titles = titles, footers = c(footers, ""), return_tab = TRUE)

xltabr::set_style_path(path)
xltabr::set_cell_format_path(cell_path)

wb <- xltabr::auto_crosstab_to_wb(ct2, titles = titles2, footers = footers2, insert_below_tab = tab)
openxlsx::openXL(wb)

# Change back to default styles
xltabr::set_style_path()
xltabr::set_cell_format_path()

Example 7: Auoindent off

ct <- reshape2::dcast(df, drive + age  ~ type, value.var= "value",  fun.aggregate = sum)
wb <- xltabr::auto_crosstab_to_wb(ct, titles = titles, footers = c(footers, ""), indent = FALSE, left_header_colnames = c("drive", "age"))
openxlsx::openXL(wb)

auto_crosstab_to_wb options

The following provides a list of all the options you can provide to auto_crosstab_to_wb

suppressMessages(devtools::document())
tools::Rd2txt_options(underline_titles = FALSE, width = 300)

tools::Rd2txt("../man/auto_crosstab_to_wb.Rd")

Advanced usage

The simple examples above wrap lower-level functions. These functions can be used to customise the output in a number of ways.

The following example shows the range of functions available.

tab <- xltabr::initialise() %>%  #Options here for providing an existing workbook, changing worksheet name, and position of table in wb
  xltabr::add_title(title_text) %>% # Optional title_style_names allows user to specify formatting
  xltabr::add_top_headers(h_list) %>% # Optional row_style_names and col_style_names allows custom formatting
  xltabr::add_body(df) %>%  #Optional left_header_colnames, row_style_names, left_header_style_names col_style names
  xltabr::add_footer(footer_text) %>% # Optional footer_style_names
  xltabr::auto_detect_left_headers() %>% # Auto detect left headers through presence of keyword, default = '(all)'
  xltabr::auto_detect_body_title_level() %>% # Auto detect level of emphasis of each row in body, through presence of keyword
  xltabr::auto_style_indent() %>% # Consolidate all left headers into a single column, with indentation to signify emphasis level
  xltabr::auto_merge_title_cells() %>% # merge the cells in the title
  xltabr::auto_merge_footer_cells() # merge the cells in the footer