Introduction to excelerate • excelerate

Introduction

This vignette demonstrates how to use the excelerate package to create supplementary tables from example datasets.

library(excelerate)

# Set up temporary directory to save the excel spreadsheets in
temp_dir <- tempdir()

Viewing Example Datasets

For our examples we will use the built-in datasets: iris, ChickWeight, ToothGrowth, and CO2.

# Load example datasets
datasets <- list(iris, ChickWeight, ToothGrowth, CO2)
lapply(datasets, head)
#> [[1]]
#>   Sepal.Length Sepal.Width Petal.Length Petal.Width Species
#> 1          5.1         3.5          1.4         0.2  setosa
#> 2          4.9         3.0          1.4         0.2  setosa
#> 3          4.7         3.2          1.3         0.2  setosa
#> 4          4.6         3.1          1.5         0.2  setosa
#> 5          5.0         3.6          1.4         0.2  setosa
#> 6          5.4         3.9          1.7         0.4  setosa
#> 
#> [[2]]
#>   weight Time Chick Diet
#> 1     42    0     1    1
#> 2     51    2     1    1
#> 3     59    4     1    1
#> 4     64    6     1    1
#> 5     76    8     1    1
#> 6     93   10     1    1
#> 
#> [[3]]
#>    len supp dose
#> 1  4.2   VC  0.5
#> 2 11.5   VC  0.5
#> 3  7.3   VC  0.5
#> 4  5.8   VC  0.5
#> 5  6.4   VC  0.5
#> 6 10.0   VC  0.5
#> 
#> [[4]]
#>   Plant   Type  Treatment conc uptake
#> 1   Qn1 Quebec nonchilled   95   16.0
#> 2   Qn1 Quebec nonchilled  175   30.4
#> 3   Qn1 Quebec nonchilled  250   34.8
#> 4   Qn1 Quebec nonchilled  350   37.2
#> 5   Qn1 Quebec nonchilled  500   35.3
#> 6   Qn1 Quebec nonchilled  675   39.2

Creating Metadata

We will use append_meta() to add comments on each of the columns in these dataframes with a description. This metadata will be presented in the README sheet of the Excel file.

The append_meta() function requires the results argument to be a dataframe and the colname_descriptions argument to be a named character vector where the names are the column names and the values are the descriptions.

iris_commented <- append_meta(
  results = iris,
  colname_descriptions = c(
    "Sepal.Length" = "Length of the sepal in cm",
    "Sepal.Width" = "Width of the sepal in cm",
    "Petal.Length" = "Length of the petal in cm",
    "Petal.Width" = "Width of the petal in cm",
    "Species" = "Species of iris"
  )
)

chickweight_commented <- append_meta(
  results = ChickWeight,
  colname_descriptions = c(
    "weight" = "Weight of the chick in grams",
    "Time" = "Time in days",
    "Chick" = "Chick identifier",
    "Diet" = "Diet type"
  )
)

toothgrowth_commented <- append_meta(
  results = ToothGrowth,
  colname_descriptions = c(
    "len" = "Tooth length in mm",
    "supp" = "Supplement type (VC or OJ)",
    "dose" = "Dose of the supplement in mg"
  )
)
co2_commented <- append_meta(
  results = CO2,
  colname_descriptions = c(
    "Plant" = "Identifier for the plant",
    "Type" = "Type of plant (Quebec or Mississippi)",
    "Treatment" = "Treatment type (nonchilled or chilled)",
    "conc" = "Concentration of CO2 in uL/L",
    "uptake" = "CO2 uptake in mg"
  )
)

Generating Supplementary Tables

Next, we demonstrate how to combine sheets into a single Excel file using the sheet(), spreadsheet() and excelerate() functions. In the below example we create two supplementary tables, each containing multiple sheets with metadata and data from the example datasets. These are saved as Excel files in the temporary directory with the names supplementary_table_1.xlsx and supplementary_table_2.xlsx.

supplementary_table_1 <- spreadsheet(
  title = "Supplementary Table 1. This is 'legend_title' from spreadsheet().",
  filename = file.path(temp_dir, "supplementary_table_1.xlsx"),
  sheet(
    iris_commented,
    "iris sheet label",
    "sheet_legend for the iris dataset."
  ),
  sheet(
    chickweight_commented,
    "ChickWeight sheet label",
    "sheet_legend for the ChickWeight dataset."
  )
)

supplementary_table_2 <- spreadsheet(
  title = "Supplementary Table 2. This is 'legend_title' from spreadsheet().",
  filename = file.path(temp_dir, "supplementary_table_2.xlsx"),
  sheet(
    toothgrowth_commented,
    "ToothGrowth sheet label",
    "sheet_legend for the ToothGrowth dataset."
  ),
  sheet(
    co2_commented,
    "CO2 sheet label",
    "sheet_legend for the CO2 dataset."
  )
)

# Save supplementary tables as .xlsx to temp_dir
excelerate(
  supplementary_table_1,
  supplementary_table_2
)
#> [[1]]
#> [1] 1
#> 
#> [[2]]
#> [1] 1

Inspect the saved Excel files

The Excel files have been saved in a temporary directory that we definied at the top of this script. You can inspect these files using the following steps:

List the files in the temporary directory to make sure they have been created.
Open the Excel files directly from R using browseURL().

# List all files in the temporary directory
list.files(temp_dir, pattern = "\\.xlsx$", full.names = TRUE)
#> [1] "/tmp/RtmpvRd0zS/supplementary_table_1.xlsx"
#> [2] "/tmp/RtmpvRd0zS/supplementary_table_2.xlsx"

# Open the files to inspect them
browseURL(file.path(temp_dir, "supplementary_table_1.xlsx"))
browseURL(file.path(temp_dir, "supplementary_table_2.xlsx"))

Conclusion

This vignette has demonstrated how to create supplementary tables using the excelerate package in R. We viewed example datasets, attached metadata to these datasets, and generated Excel spreadsheets with multiple sheets. The excelerate package simplifies the process of creating Supplementary Tables which can often be a time consuming, manual task in manuscript preparation.