Reproducible Tables

Load in Data

Let’s start with some data and a basic table, then we will build up this table to include more complex ways to structure the data. I’m going to use the titanic data that were used in the discussion of the glm() function.

library(titanic)

titanic <- bind_rows(titanic_test,
                     titanic_train)
head(titanic)

##   PassengerId Pclass                                         Name    Sex  Age
## 1         892      3                             Kelly, Mr. James   male 34.5
## 2         893      3             Wilkes, Mrs. James (Ellen Needs) female 47.0
## 3         894      2                    Myles, Mr. Thomas Francis   male 62.0
## 4         895      3                             Wirz, Mr. Albert   male 27.0
## 5         896      3 Hirvonen, Mrs. Alexander (Helga E Lindqvist) female 22.0
## 6         897      3                   Svensson, Mr. Johan Cervin   male 14.0
##   SibSp Parch  Ticket    Fare Cabin Embarked Survived
## 1     0     0  330911  7.8292              Q       NA
## 2     1     0  363272  7.0000              S       NA
## 3     0     0  240276  9.6875              Q       NA
## 4     0     0  315154  8.6625              S       NA
## 5     1     1 3101298 12.2875              S       NA
## 6     0     0    7538  9.2250              S       NA

Suppose we were interested in exploring summary statistics by the different classes of passengers (Pclass variable). We could compute the summary statistics using dplyr as follows.

titanic %>%
  group_by(Pclass) %>%
  summarise(avg_age = mean(Age, na.rm = TRUE),
            sd_age = sd(Age, na.rm = TRUE),
            avg_fare = mean(Fare, na.rm = TRUE),
            sd_fare = sd(Fare, na.rm = TRUE),
            min_fare = min(Fare, na.rm = TRUE),
            max_fare = max(Fare, na.rm = TRUE),
            perc_survived = mean(Survived, na.rm = TRUE) * 100,
            number_passengers = n()
  )

## # A tibble: 3 × 9
##   Pclass avg_age sd_age avg_fare sd_fare min_fare max_fare perc_survived
##    <int>   <dbl>  <dbl>    <dbl>   <dbl>    <dbl>    <dbl>         <dbl>
## 1      1    39.2   14.5     87.5    80.4        0    512.           63.0
## 2      2    29.5   13.6     21.2    13.6        0     73.5          47.3
## 3      3    24.8   12.0     13.3    11.5        0     69.6          24.2
## # … with 1 more variable: number_passengers <int>

This is the format that we used in the past for summary statistics. One function that is handy for creating tables from this output is the kable() function from the knitr package. This function essentially turns the output into a markdown table that will be parsed more easily when you compile the document into HTML, PDF, or even a Word document. Below is how the kable() function can be used.

titanic %>%
  group_by(Pclass) %>%
  summarise(avg_age = mean(Age, na.rm = TRUE),
            sd_age = sd(Age, na.rm = TRUE),
            avg_fare = mean(Fare, na.rm = TRUE),
            sd_fare = sd(Fare, na.rm = TRUE),
            min_fare = min(Fare, na.rm = TRUE),
            max_fare = max(Fare, na.rm = TRUE),
            perc_survived = mean(Survived, na.rm = TRUE) * 100,
            number_passengers = n()
  ) %>%
  kable()

No arguments are needed to pass to the kable() function as it can automatically detect the format desired based on the yaml front matter in an Rmd document. There are useful arguments to kable that can make the table format a bit nicer to consume.

titanic_summary <- titanic %>%
  group_by(Pclass) %>%
  summarise(avg_age = mean(Age, na.rm = TRUE),
            sd_age = sd(Age, na.rm = TRUE),
            avg_fare = mean(Fare, na.rm = TRUE),
            sd_fare = sd(Fare, na.rm = TRUE),
            min_fare = min(Fare, na.rm = TRUE),
            max_fare = max(Fare, na.rm = TRUE),
            perc_survived = mean(Survived, na.rm = TRUE) * 100,
            number_passengers = n()
  )

kable(titanic_summary, digits = 2)

kable(titanic_summary, digits = c(0, 2, 2, 2, 2, 1, 1, 3, 0))

kable(titanic_summary, digits = c(0, 2, 2, 2, 2, 1, 1, 3, 0),
        col.names = c('Class', 'Avg. Age', 'SD Age', 'Avg. Fare', 'SD Fare', 
                      'Min. Fare', 'Max. Fare', '% Survived', 'Number of Passengers'))

kable(titanic_summary, digits = c(0, 2, 2, 2, 2, 1, 1, 3, 0),
        col.names = c('Class', 'Avg. Age', 'SD Age', 'Avg. Fare', 'SD Fare', 
                      'Min. Fare', 'Max. Fare', '% Survived', 'Number of Passengers'),
        align = 'crrrrrrrr')

kable(titanic_summary, digits = c(0, 2, 2, 2, 2, 1, 1, 3, 0),
        col.names = c('Class', 'Avg. Age', 'SD Age', 'Avg. Fare', 'SD Fare', 
                      'Min. Fare', 'Max. Fare', '% Survived', 'Number of Passengers'),
        align = 'crrrrrrrr',
        caption = 'Descriptive statistics of Titanic passengers by ticket class')

More complicated tables with kableExtra

The tables created with kable() are aimed to be simple tables that are relatively quick to create with less focus on the styling of the tables. For some tables, this approach is sufficient. For other tables, the additional formatting or styling is useful to control how the table appears in the final document. This is where the kableExtra package is useful and builds off the of the kable() function that we have already used to create reproducible tables.

kable(titanic_summary, digits = c(0, 2, 2, 2, 2, 1, 1, 3, 0),
        col.names = c('Class', 'Avg. Age', 'SD Age', 'Avg. Fare', 'SD Fare', 
                      'Min. Fare', 'Max. Fare', '% Survived', 'Number of Passengers'),
        align = 'crrrrrrrr',
        caption = 'Descriptive statistics of Titanic passengers by ticket class') %>% 
  kable_styling()

kable(titanic_summary, digits = c(0, 2, 2, 2, 2, 1, 1, 3, 0),
        col.names = c('Class', 'Avg. Age', 'SD Age', 'Avg. Fare', 'SD Fare', 
                      'Min. Fare', 'Max. Fare', '% Survived', 'Number of Passengers'),
        align = 'crrrrrrrr',
        caption = 'Descriptive statistics of Titanic passengers by ticket class') %>% 
  kable_styling(bootstrap_options = c('striped', 'hover', 'condensed', 
                                      'responsive', 'bordered'))

titanic_summary_small <- titanic %>%
  group_by(Pclass) %>%
  summarise(avg_age = mean(Age, na.rm = TRUE),
            avg_fare = mean(Fare, na.rm = TRUE),
            perc_survied = mean(Survived, na.rm = TRUE) * 100,
            number_passengers = n()
  )

kable(titanic_summary_small, digits = c(0, 2, 2, 3, 0),
        col.names = c('Class', 'Avg. Age', 'Avg. Fare', '% Survived', 'Number of Passengers'),
        align = 'crrrr',
        caption = 'Descriptive statistics of Titanic passengers by ticket class') %>% 
  kable_styling(bootstrap_options = c('striped', 'hover', 'condensed'),
                full_width = FALSE)

kable(titanic_summary_small, digits = c(0, 2, 2, 3, 0),
        col.names = c('Class', 'Avg. Age', 'Avg. Fare', '% Survived', 'Number of Passengers'),
        align = 'crrrr',
        caption = 'Descriptive statistics of Titanic passengers by ticket class') %>% 
  kable_styling(bootstrap_options = c('striped', 'hover', 'condensed'),
                full_width = FALSE, position = 'right')

kable(titanic_summary_small, digits = c(0, 2, 2, 3, 0),
        col.names = c('Class', 'Avg. Age', 'Avg. Fare', '% Survived', 'Number of Passengers'),
        align = 'crrrr',
        caption = 'Descriptive statistics of Titanic passengers by ticket class') %>% 
  kable_styling(bootstrap_options = c('striped', 'hover', 'condensed'),
                full_width = FALSE, position = 'float_right')

kable(titanic_summary, digits = c(0, 2, 2, 2, 2, 1, 1, 3, 0),
        col.names = c('Class', 'Avg. Age', 'SD Age', 'Avg. Fare', 'SD Fare', 
                      'Min. Fare', 'Max. Fare', '% Survived', 'Number of Passengers'),
        align = 'crrrrrrrr',
        caption = 'Descriptive statistics of Titanic passengers by ticket class') %>% 
  kable_styling(bootstrap_options = c('striped', 'hover', 'condensed'),
                font_size = 10)

Column or Row Control

It is possible to format entire columns or rows within a table. This can be useful if there is an entire row or column that should be bold, italic, a different background color, or font color. These can be controlled at the column and row levels with the column_spec() or row_spec() functions respectively.

These two function behave very similarly, with each function taking a column or row number as the first argument. Ranges of columns or rows are also possible to specify when declaring a column or row number. Subsequent arguments are the formatting arguments for that specific column or row. Examples or arguments are: bold, italic, color, background, width, or angle which make the font bold, italic, change the font color, change the background color, alter the width of the columns, or change the angle of the text respectively.

Suppose given the table above, we wanted to make the “% Survived” and “Number of Passengers” columns bold and we wished to make the class three row italic change the background color to a light red.

kable(titanic_summary, digits = c(0, 2, 2, 2, 2, 1, 1, 3, 0),
        col.names = c('Class', 'Avg. Age', 'SD Age', 'Avg. Fare', 'SD Fare', 
                      'Min. Fare', 'Max. Fare', '% Survived', 'Number of Passengers'),
        align = 'crrrrrrrr',
        caption = 'Descriptive statistics of Titanic passengers by ticket class') %>% 
  kable_styling(bootstrap_options = c('striped', 'hover', 'condensed')) %>%
  column_spec(8:9, bold = TRUE, width = '0.75in') %>%
  row_spec(3, italic = TRUE, background = "#FF6347")

You’ll notice that the bold and italic arguments are logical, specifying TRUE turns the attribute on. Secondly, the identification of the column and rows that are to be formatted is done with column and row numbers respectively. When declaring the row number, the header row is skipped. The header can be formatted using the row_spec(), however you would specify the row number as 0. Finally, names of colors that are valid within R can be used as a character string, (use colors() to see all the colors defined within R), but hex codes can also be specified which can give further control. The color argument works identically to the background argument. Finally, the width argument was used which forced the last two columns to be 0.75 inches wide respectively. This argument can be particularly useful for columns that have text strings that are long.

Cell Control

Cell control is another way to format a table, but this time the formatting is done within the cells with the cell_spec() function. Another way to think about this is conditional logic for specific cells in the table. This process is done before being passed to the kable() function via a call to mutate(). Suppose for example, we wanted to color the cells differently for the percentage survival greater than 50% compared to those less than 50%. Here is an example of doing this.

titanic_summary %>% 
  mutate(
    perc_survived = cell_spec(round(perc_survived, 3), 
                              color = ifelse(perc_survived > 50, 
                                                  '#440154', '#FDE725'))
  ) %>% 
  kable(escape = FALSE, digits = c(0, 2, 2, 2, 2, 1, 1, 3, 0),
        col.names = c('Class', 'Avg. Age', 'SD Age', 'Avg. Fare', 'SD Fare', 
                      'Min. Fare', 'Max. Fare', '% Survived', 'Number of Passengers'),
        align = 'crrrrrrrr',
        caption = 'Descriptive statistics of Titanic passengers by ticket class') %>% 
  kable_styling(bootstrap_options = c('striped', 'hover', 'condensed')) %>%
  column_spec(8:9, bold = TRUE, width = '0.75in')

A couple of things to note about the cell_spec() function. When using cell_spec(), the additional argument, escape = FALSE to the kable() function must be specified. In addition, notice the round() function used within the mutate() function. This is needed to ensure that the digits are rounded as this column will be ignored by the digits argument from the kable() function. Additional formatting can also still be passed using the column_spec() function after the cell_spec() function was used. This is done here to control the width and bold font specification.

titanic_summary %>% 
  mutate(
    perc_survived = cell_spec(round(perc_survived, 3), 
                              color = ifelse(perc_survived > 50, 
                                                  '#440154', '#FDE725')),
    number_passengers = color_bar("#FF6347")(number_passengers)
  ) %>% 
  kable(escape = FALSE, digits = c(0, 2, 2, 2, 2, 1, 1, 3, 0),
        col.names = c('Class', 'Avg. Age', 'SD Age', 'Avg. Fare', 'SD Fare', 
                      'Min. Fare', 'Max. Fare', '% Survived', 'Number of Passengers'),
        align = 'crrrrrrrr',
        caption = 'Descriptive statistics of Titanic passengers by ticket class') %>% 
  kable_styling(bootstrap_options = c('striped', 'hover', 'condensed')) %>%
  column_spec(8, bold = TRUE, width = '0.75in') %>% 
  column_spec(9, bold = TRUE)

Grouped Columns or Rows

The last element of the table formatting that will be covered is the ability to add grouped columns or rows. The add_header_above() function gives the user the ability to add additional header columns above the currently defined header(s). This is helpful to add a grouping label that spans multiple columns and remove redundancy in the header labels across columns. For example, if you look at the original titanic_summary data object created previously, there are two columns representing descriptive statistics for the age variable and four columns for the fare variable. The age and fare variable labels can be removed and added as an additional header. The add_header_above() function takes a character string representing the names of the new header labels and the number of columns that these labels should span.

kable(titanic_summary, digits = c(0, 2, 2, 2, 2, 1, 1, 3, 0),
        col.names = c('Class', 'Avg.', 'SD', 'Avg.', 'SD', 
                      'Min.', 'Max.', '% Survived', 'Number of Passengers'),
        align = 'crrrrrrrr',
        caption = 'Descriptive statistics of Titanic passengers by ticket class') %>% 
  kable_styling(bootstrap_options = c('striped', 'hover', 'condensed', 'bordered')) %>% 
  add_header_above(c("", "Age" = 2, "Fare" = 4, "", ""))

titanic_summary_male <- titanic %>%
  filter(Sex == 'male') %>%
  group_by(Pclass) %>%
  summarise(avg_age = mean(Age, na.rm = TRUE),
            avg_fare = mean(Fare, na.rm = TRUE),
            perc_survied = mean(Survived, na.rm = TRUE) * 100,
            number_passengers = n()
  )
titanic_summary_female <- titanic %>%
  filter(Sex == 'female') %>%
  group_by(Pclass) %>%
  summarise(avg_age = mean(Age, na.rm = TRUE),
            avg_fare = mean(Fare, na.rm = TRUE),
            perc_survied = mean(Survived, na.rm = TRUE) * 100,
            number_passengers = n()
  )
titanic_summary_sex <- dplyr::bind_rows(
  titanic_summary_male,
  titanic_summary_female,
  titanic_summary_small
)
titanic_summary_sex

## # A tibble: 9 × 5
##   Pclass avg_age avg_fare perc_survied number_passengers
##    <int>   <dbl>    <dbl>        <dbl>             <int>
## 1      1    41.0     69.9         36.9               179
## 2      2    30.8     19.9         15.7               171
## 3      3    26.0     12.4         13.5               493
## 4      1    37.0    109.          96.8               144
## 5      2    27.5     23.2         92.1               106
## 6      3    22.2     15.3         50                 216
## 7      1    39.2     87.5         63.0               323
## 8      2    29.5     21.2         47.3               277
## 9      3    24.8     13.3         24.2               709

kable(titanic_summary_sex, digits = c(0, 2, 2, 3, 0),
        col.names = c('Class', 'Avg. Age', 'Avg. Fare', '% Survived', 'Number of Passengers'),
        align = 'crrrr',
        caption = 'Descriptive statistics of Titanic passengers by ticket class') %>% 
  kable_styling(bootstrap_options = c('striped', 'hover', 'condensed'),
                full_width = FALSE) %>%
  kableExtra::group_rows('Male', 1, 3) %>%
  kableExtra::group_rows('Female', 4, 6) %>%
  kableExtra::group_rows('Total', 7, 9)

Cross-referencing tables

The final topic to discuss briefly is cross-referencing of tables in Rmd documents. Cross-referencing allows the user to allow for the labeling of table numbers to be done dynamically and automatically rather than looking at the table numbers and updating if these change. The chunk label name from the Rmd chunk is used for the identification of the label name for cross-referencing. Specifically, cross-referencing a table would take this general form: @\ref(tab:chunkname) where the “chunkname” portion would be replaced with the name used for the table chunk. For example, when creating the chunk below to produce the table, the chunk name, “cross-reference” was used, therefore the cross-referencing code would look like the following: @\ref(tab:cross-reference). Therefore, one could write, explore Table “@(tab:cross-reference)” for descriptive statistics by ticket class for males, females, and total numbers. Note: I put the citation portion in quotes as these do not work in R notebook output, but should work if you compile the Rmd document to HTML or PDF.

kable(titanic_summary_sex, digits = c(0, 2, 2, 3, 0),
        col.names = c('Class', 'Avg. Age', 'Avg. Fare', '% Survived', 'Number of Passengers'),
        align = 'crrrr') %>% 
  kable_styling(bootstrap_options = c('striped', 'hover', 'condensed'),
                full_width = FALSE) %>%
  kableExtra::group_rows('Male', 1, 3) %>%
  kableExtra::group_rows('Female', 4, 6) %>%
  kableExtra::group_rows('Total', 7, 9)

The final note about cross-references, is that the table caption needs to be specified as a Rmd chunk option, not within the kable() function. The caption argument is specified the same as when specified with the kable() function, therefore this could be copied and pasted from the previous examples.

Additional Resources

Below are some additional resources for working with reproducible tables in R.

• kableExtra HTML Vignette
• kableExtra PDF Vignette
• kableExtra with formattable
• gt package (in active development)