Getting started with xlr

In xlr: Create Table Summaries and Export Neat Tables to 'Excel'

knitr::opts_chunk$set(
  collapse = TRUE,
  comment = "#>"
)
library(xlr)
# delete the example files if they exist already
if (file.exists("example.xlsx")) file.remove("example.xlsx")
if (file.exists("example2.xlsx")) file.remove("example2.xlsx")
options(tibble.print_min = 4L, tibble.print_max = 4L)
set.seed(123456)

xlr is designed to help with the analysis of survey data and conduct modelling, and then export this information to Excel in an easy and consistent way.

It is designed to help people with little R experience analyse survey data quickly and easily. This includes functions to quickly analyse multiple-response questions and block questions.

This package also provides a simple and easy to use interface with Excel, building on the excellent work of the [openxlsx] package. It is designed with ease of use in mind, at the expense of some functionality of openxlsx.

This package relies extensively on the tidyverse and so we use many of the same terms and concepts here.

There are three aspects to this package:

1. Functions to help with the analysis of survey complex survey data.

2. Functions to help export this data to Excel.

3. New R types to support consistent formatting in Excel.

Data: clothes_opinions

To explore how to analyse complex data we'll use the dataset clothes_opinions. This dataset contains fake survey data with peoples opinions on clothes as well as a series of demographic characteristics. It is documented in ?clothes_opinions.

dim(clothes_opinions)
clothes_opinions

clothes_opinions is a tibble a adapted type of data.frame. You can learn more about tibbles at https://tibble.tidyverse.org.

It was designed to mimic data initially saved as a .sav file and imported with haven::import_spss(). Data with this form can easily be exported from major survey platforms such as Qualtrics, and includes useful information such as question labels which xlr functions can utilise automatically.

Analysing survey data

There are three main functions to analyse survey data:

• build_table() which creates 1, 2, 3, ... -way tables.

• build_mtable() which creates 1, 2, 3, ... -way tables for multiple response questions.

• build_qtable() which creates 1, 2, 3, ... -way tables for a block of questions.

We will start by introducing xlr_table() and go through all of its functionality. This functionality is similar for build_mtable() and build_qtable().

build_table()

This function provides roughly the same functionality as base R's table() except it provides a lot more support for easy use. Like all functions in this package, we follow the convention of the first argument is the data.frame (or tibble) that you want to work on. The second argument are the columns we want to build a table with, and the remainder are options for build_table().

Let's first calculate the number of people of each gender in clothes_opinions:

clothes_opinions |>
  build_table(gender2)

You can see that we have outputted a table which shows the number of people and the percentage of each gender in our data set.

You can easily create two or three way tables by passing additional columns to the cols argument:

clothes_opinions |> 
  build_table(c(age_group, gender2),
              table_title = "Gender by age make up of clothing opinion data")

clothes_opinions |> 
  build_table(c(age_group, gender2, Q1_1),
              table_title = "Responses to Q1_1 by age and gender")

The cols argument uses tidy selections to select the columns we want to make a table with. See \<tidy-select> for more details on the selectors. This should be familiar for people who use dplyr verbs.

The data returns has a long format by default. Currently wide table table is unsupported but may be included in a future update.

Introduction to xlr_table()

You can also see that it has a special type xlr_table(). This is a S3 class defined by xlr to help output the data to Excel. It contains the table data, a table title and a footnote, as well as hidden data to help format the table in Excel. You can learn more about xlr_table() in the vignette to do.

You can directly pass a title and a footnote to build_table() by passing them as arguments to build_table():

clothes_opinions |>
  build_table(gender2,
              table_title = "Gender make up of clothing opinion data",
              footnote = "This shows that the data has a representative sample.")

Labels in xlr

Now if you were paying close attention, you may have noticed that clothes_opinions$gender2 went from being haven_labelled to a xlr_vector(). A xlr_vector is a wrapper around other types (see below for more details), and the base type is character(). All build_* functions will convert this data automatically, as well as pull information on the labels. See haven's documentation for more details about the labelled type: https://haven.tidyverse.org/reference/labelled.html.

Additionally, if your data is labelled, you can pull out the question label metadata automatically and include it in the footnote by specifying use_questions = TRUE. Columns that don't have a label are ignored.

clothes_opinions |>
  build_table(c(age_group, gender2),
              table_title = "Gender by age make up of clothing opinion data",
              use_question = TRUE)

Weights

Weights are supported, you only need to pass the column name for the weights to the wt argument:

clothes_opinions |>
  build_table(c(age_group, gender2),
              table_title = "Gender by age make up of clothing opinion data (weighted)",
              wt = weight)

Missing data

build_table() uses only complete cases by default, that is for one of the columns we are if a row includes a single NA it is removed from the table calculation. To include NA values set use_NA = TRUE. This will mean that ALL NA's will be included in the calculation of the table:

clothes_opinions |>
  build_table(c(group, age_group),
              table_title = "Survey group by age make up of clothing opinion data",
              use_NA = TRUE)

If you would like to include the NA values of only one or some of the columns we recommend using dplyr::filter() as demonstrated below.

clothes_opinions |>
  # remove all the rows where group is missing
  dplyr::filter(!is.na(group)) |>
  # by setting use_NA to true we keep the NA's from the age_group column
  build_table(c(group, age_group),
              table_title = "Survey group by age make up of clothing opinion data",
              use_NA = TRUE)

build_mtable()

This function is design to analyse multiple response questions in survey data. To do so the data must be wide data with one response per column (and the remaining responses NA). This is how Qualtrics exports this data by default. The format is shown below:

clothes_opinions |>
  dplyr::select(starts_with("Q2"))

To create a table using the mcol argument to specify the mutliple response columns you would like to create a table with:

clothes_opinions |>
  build_mtable("Q2")

The output N count is the number of people that responded to each multiple response option, the N_group is the total number of people that responded to this question. The Percent is the percentage of people that responded to each of the options. As you can respond to multiple options this percentage will likely not add up to 100.

Note, internally build_mtable() uses tidyselect::starts_with() to select the columns that you would like to build a table with, because of this you need to pass a string to mcol.

You can cut your multiple response column by other data by passing columns to the cols argument. As with build_table() it uses <tidy-eval> to select the columns.

clothes_opinions |>
  build_mtable(mcol = "Q2",
               cols = age_group)

The N_group for this question is the number of people in each age group that responded to the multiple response question.

It is common that a mulitple response column will include an other response in a survey. This column needs to be removed before you analyse the data with build_mtable(). We recommend using dplyr::select as demonstrated below:

clothes_opinions |>
  dplyr::select(-Q3_other) |>
  build_mtable(mcol = "Q3",
               cols = age_group)

As above you can see the build_mtable() automatically converts haven labelled data to a xlr_vector().

Multiple multiple response columns

You can pass up to two multiple response columns to build_mtable(), any more and we recommend filtering before hand.

clothes_opinions |>
  dplyr::select(-Q3_other) |>
  build_mtable(mcol = c("Q2","Q3"))

The N_group is the number of people in each age_group that responded to one of the options in age_group. N is the number of people that responded to the multiple response question in each group.

build_qtable()

This function is designed to help analyse a block of questions. A block of questions is where all the responses use the same scale (it is usually a matrix question). The data should have the form:

clothes_opinions |>
  dplyr::select(starts_with("Q1"))

To analyse a question block, you need to pass a <tidy-eval> selection to the function to select all columns in the question block. See we pass the same statement we made in the select statement above.

clothes_opinions |>
  build_qtable(starts_with("Q1"))

# You can also select the columns directly
clothes_opinions |>
  build_qtable(c(Q1_1,Q1_2,Q1_3,Q1_4))

You can cut all the columns in the question block by another column(s) by specifying the cols argument. As with build_table() it uses <tidy-eval> to select the columns.

clothes_opinions |>
  build_qtable(starts_with("Q1"),
               gender2)

xlr_table(), xlr types and writing to Excel

A key part of the xlr is to export data to Excel in an easy, and user friendly way. The workhorse function in this scenario is xlr_table(), and if you like the default options is meant to be the function that you will mostly use.

A xlr_table() is a tibble::tibble() with an optional title, and footnote, as well as a number of formatting options.

clothes_opinions |>
  xlr_table("This is a title",
             "this is a footnote with extra information")

When you call xlr_table() it converts the elements of your table to different xlr types.

Types

xlr types are defined to help with the formatting of columns when they are output to Excel. There are four difference types defined in xlr:

• xlr_numeric() to format doubles neatly.

• xlr_integer() to format integer data neatly.

• xlr_percent() to format numeric data as a percentage.

• xlr_vector() a general type to format data nicely without specific rules.

All of the above variables contain the argument xlr_format which only takes a xlr_format() object. xlr_format() allows you to control different formatting options when the data is outputted to Excel, it currently does not change how the data looks in console. You can change the font size, colour, text position etc. with xlr_format(). See ?xlr_format for the full range of options.

For xlr_numeric() and xlr_percent() you can also set the number of decimal places through setting dp=x.

Updating/Formatting columns

You can update the format of individual columns by using dplyr::mutate or base R by setting the column with new formatting options:

table <- xlr_table(mtcars, "A clever title", "A useful footnote")

# Lets update the format of the mpg column so that it displays using 0 decimal places
table$mpg <- xlr_numeric(table$mpg, dp = 0)

# You can also use mutate to achieve the same thing, this is useful for
# updating multiple columns either by using across or in a single statement
table <- table |>
  dplyr::mutate(
    mpg = xlr_numeric(mpg, dp = 0),
    # convert columns that are integers to xlr_integer type
    across(vs:carb, ~ xlr_integer(.x))
  )

dplyr verbs

The xlr_table() type is implemented to work with most dplyr verbs to make working with the data as seamless as possible. To find out more see ?xlr_and_dplyr.

Writing data to Excel

xlr makes writing data to Excel easy using the write_xlsx() function. This function takes either a xlr_table, tibble or data.frame. Note when you output a single object you need to specify

write_xlsx(mtcars,
           file = "example.xlsx",
           sheet_name = "example_sheet")

The output looks like this in Excel:

When you output a xlr_table with this function additional formatting will be applied to the data as well as the title and a footnote.

write_xlsx(table,
           file = "example.xlsx",
           sheet_name = "example_sheet")

The output looks like this in Excel:

To update this formatting you either need to update the styles of the columns using the above, or if you want to modify the style of the table use update_theme. You can modify the the format of the title, footnote, column heading or table body. The below example shows how to update title colour to be red and the underlined:

table <- update_theme(table,
                      title_format = xlr_format(font_colour = "red",
                                                 text_style = "underline"))
write_xlsx(table,
           file = "example.xlsx",
           sheet_name = "example_sheet")

The output looks like this:

See ?update_theme for more details.

write_xlsx() and list()

Like openxlsx::write.xlsx() you can also pass a named list to write_xlsx() and these will be automatically created as sheets in the Excel file. If you have a list() of tables, set TOC = TRUE in order to generate a table of contents for the Excel file. This is particularly useful when you have a large number of tables.

Putting it all together

Example of how you can use xlr to analyse a survey is below:

output_list <- list()

output_list[["gender"]] <- build_table(clothes_opinions,
                                       gender2,
                                       "Gender in clothes opinions survey")


output_list[["gender age"]] <- build_table(clothes_opinions,
                                       c(gender2, age_group),
                                       "Gender by age in clothes opinions survey")

output_list[["gender age"]] <- build_table(clothes_opinions,
                                       c(gender2, age_group),
                                       "Gender by age in clothes opinions survey")

output_list[["opinions"]] <- build_qtable(clothes_opinions,
                                        starts_with("Q1"),
                                       table_title = "Opinions on different clothing items")

# Sometimes it is neater to use the pipe operator on the data
# This also allows auto completion in RStudio for variable names
output_list[["opinions gender"]] <- 
  clothes_opinions |>
    build_qtable(starts_with("Q1"),
                  gender2,
                 table_title = "Opinions on different clothing items by gender2",
                 use_questions = TRUE)

# now output the data, we turn on the option to generate a table of contents
write_xlsx(output_list,
           file = "example2.xlsx",
           TOC = TRUE)

# delete the example files if they exist already
if (file.exists("example.xlsx")) file.remove("example.xlsx")
if (file.exists("example2.xlsx")) file.remove("example2.xlsx")

xlr documentation built on April 3, 2025, 6:07 p.m.