flextable selectors

Why selectors

Selectors can be used to specify the rows and columns where an operation should happened.

Many flextable functions have selectors i and j: bg, bold, border, color, padding, fontsize, italic, align, compose, …

It makes conditional formatting very easy. As the underlying datasets (for body but also header and footer parts) are available, selectors can easily be used and operations can be seamlessly piped (with magrittr::%>%).

qflextable(head(iris)) %>% 
  color(~ Sepal.Length < 5, color = "orange", ~ Sepal.Width + Petal.Length ) %>% 
  color(~ Sepal.Length > 4.99, ~ Sepal.Length, color = "red")

Sepal.Length	Sepal.Width	Petal.Length	Petal.Width	Species
5.1	3.5	1.4	0.2	setosa
4.9	3.0	1.4	0.2	setosa
4.7	3.2	1.3	0.2	setosa
4.6	3.1	1.5	0.2	setosa
5.0	3.6	1.4	0.2	setosa
5.4	3.9	1.7	0.4	setosa

Default values for i and j are NULL, NULL value is interpreted as all rows or all columns.

Usage

i for rows selection and j for columns selection can be expressed in different ways:

as a formula

Use i = ~ Species %in% "versicolor" to select all rows where values from column ‘Species’ are “versicolor”.

The argument expression is ~ and then an R expression. There is no need to quote variable, when the formula will be evaluated, values from the corresponding dataset (to the part) will be used.

To express multiple conditions, use operator & or |:

i = ~ Sepal.Length < 5 & Species %in% "versicolor"

The columns can be selected with a formula also. Use operator + for multiple columns. To select columns Species and Sepal.Length: j = ~ Species + Sepal.Length

ft <- flextable(head(iris, n = 10))
ft <- color(ft, 
            i = ~ Sepal.Length < 5, 
            j = ~ Sepal.Length + Sepal.Width, 
            color = "orange")
ft

Sepal.Length	Sepal.Width	Petal.Length	Petal.Width	Species
5.1	3.5	1.4	0.2	setosa
4.9	3.0	1.4	0.2	setosa
4.7	3.2	1.3	0.2	setosa
4.6	3.1	1.5	0.2	setosa
5.0	3.6	1.4	0.2	setosa
5.4	3.9	1.7	0.4	setosa
4.6	3.4	1.4	0.3	setosa
5.0	3.4	1.5	0.2	setosa
4.4	2.9	1.4	0.2	setosa
4.9	3.1	1.5	0.1	setosa

as a character vector

Argument j supports simple character vector containing the col_key names.

dat <- head(iris, n = 10)
ft <- flextable(dat)
ft <- color(ft, j = "Sepal.Length", color = "orange", part = "all")
ft <- bold(ft, j = c("Sepal.Width", "Species"), bold = TRUE)
ft

Sepal.Length	Sepal.Width	Petal.Length	Petal.Width	Species
5.1	3.5	1.4	0.2	setosa
4.9	3.0	1.4	0.2	setosa
4.7	3.2	1.3	0.2	setosa
4.6	3.1	1.5	0.2	setosa
5.0	3.6	1.4	0.2	setosa
5.4	3.9	1.7	0.4	setosa
4.6	3.4	1.4	0.3	setosa
5.0	3.4	1.5	0.2	setosa
4.4	2.9	1.4	0.2	setosa
4.9	3.1	1.5	0.1	setosa

as a integer vector

Each element is the row number or col_key number:

ft <- flextable(head(iris, n = 10))
ft <- color(ft, i = 1:3, j = 1:3, color = "orange")
ft

Sepal.Length	Sepal.Width	Petal.Length	Petal.Width	Species
5.1	3.5	1.4	0.2	setosa
4.9	3.0	1.4	0.2	setosa
4.7	3.2	1.3	0.2	setosa
4.6	3.1	1.5	0.2	setosa
5.0	3.6	1.4	0.2	setosa
5.4	3.9	1.7	0.4	setosa
4.6	3.4	1.4	0.3	setosa
5.0	3.4	1.5	0.2	setosa
4.4	2.9	1.4	0.2	setosa
4.9	3.1	1.5	0.1	setosa

as a logical vector

dat <- head(iris, n = 10)
ft <- flextable(dat)

ft <- color(ft, i = rep(c(TRUE, FALSE), 5), color = "orange")
ft

Sepal.Length	Sepal.Width	Petal.Length	Petal.Width	Species
5.1	3.5	1.4	0.2	setosa
4.9	3.0	1.4	0.2	setosa
4.7	3.2	1.3	0.2	setosa
4.6	3.1	1.5	0.2	setosa
5.0	3.6	1.4	0.2	setosa
5.4	3.9	1.7	0.4	setosa
4.6	3.4	1.4	0.3	setosa
5.0	3.4	1.5	0.2	setosa
4.4	2.9	1.4	0.2	setosa
4.9	3.1	1.5	0.1	setosa

Selectors and flextable parts

Several operations (bold, color, padding, compose) accept part="all". In this case all mean to apply to each part of the flextable (header, body and footer if any). That’s useful for many cases:

add a vertical line (vline) to one or several column in header and body part, use part="all", j = c('col1', 'col2') as selector.

border <- officer::fp_border()
ft <- flextable(head(iris, n = 10))
ft <- vline(ft, j = c('Sepal.Length', 'Sepal.Width'), border = border, part = "all")
ft

Sepal.Length	Sepal.Width	Petal.Length	Petal.Width	Species
5.1	3.5	1.4	0.2	setosa
4.9	3.0	1.4	0.2	setosa
4.7	3.2	1.3	0.2	setosa
4.6	3.1	1.5	0.2	setosa
5.0	3.6	1.4	0.2	setosa
5.4	3.9	1.7	0.4	setosa
4.6	3.4	1.4	0.3	setosa
5.0	3.4	1.5	0.2	setosa
4.4	2.9	1.4	0.2	setosa
4.9	3.1	1.5	0.1	setosa

change color only on the first row of the header part, use part="header", i = 1 as selector.

ft <- color(ft, i = 1, color = "red", part = "header")
ft

Sepal.Length	Sepal.Width	Petal.Length	Petal.Width	Species
5.1	3.5	1.4	0.2	setosa
4.9	3.0	1.4	0.2	setosa
4.7	3.2	1.3	0.2	setosa
4.6	3.1	1.5	0.2	setosa
5.0	3.6	1.4	0.2	setosa
5.4	3.9	1.7	0.4	setosa
4.6	3.4	1.4	0.3	setosa
5.0	3.4	1.5	0.2	setosa
4.4	2.9	1.4	0.2	setosa
4.9	3.1	1.5	0.1	setosa

change color in column ‘col1’ and ‘col3’ in body part when values of ‘col2’ are negative, use part="body", i = ~ col2 < 0, j = c('col1', 'col3) as selector.

ft <- color(ft, i = ~ Sepal.Length < 5, 
               j = c('Petal.Length', 'Petal.Width'), 
               color = "red", part = "body")
ft

Sepal.Length	Sepal.Width	Petal.Length	Petal.Width	Species
5.1	3.5	1.4	0.2	setosa
4.9	3.0	1.4	0.2	setosa
4.7	3.2	1.3	0.2	setosa
4.6	3.1	1.5	0.2	setosa
5.0	3.6	1.4	0.2	setosa
5.4	3.9	1.7	0.4	setosa
4.6	3.4	1.4	0.3	setosa
5.0	3.4	1.5	0.2	setosa
4.4	2.9	1.4	0.2	setosa
4.9	3.1	1.5	0.1	setosa

The most efficient selector for rows is the formula expression.

The most efficient selector for columns is the character vector.

Selectors expressed as formula connot be used everywhere:

Rows selector expressed as formula cannot be used with header part. The header part contains only character values.
A common value for argument part is “all”. In this case, a
row selector expressed as a formula is not supported (it can not as header dataset is made of only character columns and body dataset is the original dataset).