Header font and merge vertically in flextable in R
I have a data frame in R called df :
library(tidyverse)
library(officer)
library(flextable)
df = df%>%
arrange(
Category,
factor(Favor, levels = c("Bad", "Mediocre", "Good")),
Year
) %>%
pivot_wider(
names_from = c(Favor, Year),
values_from = Percentage,
names_sep = "-"
)
# Create the flextable
ft <- flextable(df)
# Apply conditional formatting
for (col in names(df)) {
if (grepl("Bad", col)) {
# Rows with "Unfavorable" > 30 (Red)
ft <- color(ft, i = which(df[[col]] > 30), j = col, color = "black")
ft <- bg(ft, i = which(df[[col]] > 30), j = col, bg = "#ed2e1c")
# Rows with "Unfavorable" between 20 and 30 (Light red)
ft <- bg(ft, i = which(df[[col]] > 20 & df[[col]] <= 30), j = col, bg = "#e09c95")
}
if (grepl("Mediocre", col)) {
# Rows with "Neutral" > 30 (Blue)
ft <- color(ft, i = which(df[[col]] > 30), j = col, color = "black")
ft <- bg(ft, i = which(df[[col]] > 30), j = col, bg = "#85c1e9")
}
if (grepl("Good", col)) {
ft <- color(ft, i = which(df[[col]] > 75), j = col, color = "black")
ft <- bg(ft, i = which(df[[col]] > 75), j = col, bg = "#04B431")
ft <- bg(ft, i = which(df[[col]] > 65 & df[[col]] <= 75), j = col, bg = "#7FF98B")
}
}
custom_border = officer::fp_border(color = "black", width = 1)
ft
# Display the flextable with borders and color
ft %>%
flextable::border_outer(part = "all", border = custom_border) %>% # Add outer borders
flextable::border(i = NULL, j = 1, border.left = custom_border) %>% # Add left border
flextable::border(i = NULL, j = ncol(df), border.right = custom_border) %>% # Add right border
flextable::hline(border = custom_border, part = "body") %>% # Add horizontal lines between rows
flextable::vline(border = custom_border, part = "all") %>%
flextable::align(j = 2:ncol(df), align = "center", part = "all") %>%
flextable::set_header_labels(ft,
Category = " ",
"Bad-1998" = "Bad",
"Bad-1999" = "Bad",
"Bad-2001" = "Bad",
"Bad-2002" = "Bad",
"Mediocre-1998" = "Mediocre",
"Mediocre-1999" = "Mediocre",
"Mediocre-2001" = "Mediocre",
"Mediocre-2002" = "Mediocre",
"Mediocre-1998" = "Mediocre",
"Mediocre-1999" = "Mediocre",
"Mediocre-2001" = "Mediocre",
"Mediocre-2002" = "Mediocre") %>%
add_header_row(values = c("Categories",
"group a",
"group b",
"group c"), colwidths = c(1, 4, 4, 4)) %>%
flextable::color(i = 1, j = 2, color = "#ed2e1c", part = "header") %>%
flextable::color(i = 1, j = 2, color = "darkgreen", part = "header") %>%
flextable::bold(part = "header") %>% # Make the header text bold
flextable::width(j = 1, width = 2) %>%
flextable::width(j = c(2,5,8), width = 1)
The problem is that I want the first two row of first column to be merged and to be named "Categories" and the font of the "group b" in header to be green.
How can I do it with flextable ?
Data
df = structure(list(Year = c(2002, 2002, 2002, 2002, 2002, 2002, 2002,
2002, 2002, 2002, 2002, 2002, 2002, 2002, 2002, 2002, 2002, 2002,
2002, 2002, 2002, 2002, 2002, 2002, 2002, 2002, 2002, 2002, 2002,
2002, 2002, 2002, 2002, 2002, 2002, 2002, 2002, 2002, 2002, 2002,
2002, 2002, 2002, 2002, 2002, 2002, 2002, 2002, 1998, 1998, 1998,
1998, 1998, 1998, 1998, 1998, 1998, 1998, 1998, 1998, 1998, 1998,
1998, 1998, 1998, 1998, 1998, 1998, 1998, 1998, 1998, 1998, 1998,
1998, 1998, 1998, 1998, 1998, 1998, 1998, 1998, 1998, 1998, 1998,
1998, 1998, 1998, 1998, 1998, 1998, 1998, 1998, 1998, 1998, 1998,
1998, 1999, 1999, 1999, 1999, 1999, 1999, 1999, 1999, 1999, 1999,
1999, 1999, 1999, 1999, 1999, 1999, 1999, 1999, 1999, 1999, 1999,
1999, 1999, 1999, 1999, 1999, 1999, 1999, 1999, 1999, 1999, 1999,
1999, 1999, 1999, 1999, 1999, 1999, 1999, 1999, 1999, 1999, 1999,
1999, 1999, 1999, 1999, 1999, 2001, 2001, 2001, 2001, 2001, 2001,
2001, 2001, 2001, 2001, 2001, 2001, 2001, 2001, 2001, 2001, 2001,
2001, 2001, 2001, 2001, 2001, 2001, 2001, 2001, 2001, 2001, 2001,
2001, 2001, 2001, 2001, 2001, 2001, 2001, 2001, 2001, 2001, 2001,
2001, 2001, 2001, 2001, 2001, 2001, 2001, 2001, 2001), Category = c("A",
"A", "A", "B", "B", "B", "C", "C", "C", "D", "D", "D", "E", "E",
"E", "F", "F", "F", "G", "G", "G", "H", "H", "H", "I", "I", "I",
"J", "J", "J", "K", "K", "K", "L", "L", "L", "M", "M", "M", "N",
"N", "N", "O", "O", "O", "P", "P", "P", "A", "A", "A", "B", "B",
"B", "C", "C", "C", "D", "D", "D", "E", "E", "E", "F", "F", "F",
"G", "G", "G", "H", "H", "H", "I", "I", "I", "J", "J", "J", "K",
"K", "K", "L", "L", "L", "M", "M", "M", "N", "N", "N", "O", "O",
"O", "P", "P", "P", "A", "A", "A", "B", "B", "B", "C", "C", "C",
"D", "D", "D", "E", "E", "E", "F", "F", "F", "G", "G", "G", "H",
"H", "H", "I", "I", "I", "J", "J", "J", "K", "K", "K", "L", "L",
"L", "M", "M", "M", "N", "N", "N", "O", "O", "O", "P", "P", "P",
"A", "A", "A", "B", "B", "B", "C", "C", "C", "D", "D", "D", "E",
"E", "E", "F", "F", "F", "G", "G", "G", "H", "H", "H", "I", "I",
"I", "J", "J", "J", "K", "K", "K", "L", "L", "L", "M", "M", "M",
"N", "N", "N", "O", "O", "O", "P", "P", "P"), Favor = c("Good",
"Mediocre", "Bad", "Good", "Mediocre", "Bad", "Good", "Mediocre",
"Bad", "Good", "Mediocre", "Bad", "Good", "Mediocre", "Bad",
"Good", "Mediocre", "Bad", "Good", "Mediocre", "Bad", "Good",
"Mediocre", "Bad", "Good", "Mediocre", "Bad", "Good", "Mediocre",
"Bad", "Good", "Mediocre", "Bad", "Good", "Mediocre", "Bad",
"Good", "Mediocre", "Bad", "Good", "Mediocre", "Bad", "Good",
"Mediocre", "Bad", "Good", "Mediocre", "Bad", "Good", "Mediocre",
"Bad", "Good", "Mediocre", "Bad", "Good", "Mediocre", "Bad",
"Good", "Mediocre", "Bad", "Good", "Mediocre", "Bad", "Good",
"Mediocre", "Bad", "Good", "Mediocre", "Bad", "Good", "Mediocre",
"Bad", "Good", "Mediocre", "Bad", "Good", "Mediocre", "Bad",
"Good", "Mediocre", "Bad", "Good", "Mediocre", "Bad", "Good",
"Mediocre", "Bad", "Good", "Mediocre", "Bad", "Good", "Mediocre",
"Bad", "Good", "Mediocre", "Bad", "Good", "Mediocre", "Bad",
"Good", "Mediocre", "Bad", "Good", "Mediocre", "Bad", "Good",
"Mediocre", "Bad", "Good", "Mediocre", "Bad", "Good", "Mediocre",
"Bad", "Good", "Mediocre", "Bad", "Good", "Mediocre", "Bad",
"Good", "Mediocre", "Bad", "Good", "Mediocre", "Bad", "Good",
"Mediocre", "Bad", "Good", "Mediocre", "Bad", "Good", "Mediocre",
"Bad", "Good", "Mediocre", "Bad", "Good", "Mediocre", "Bad",
"Good", "Mediocre", "Bad", "Good", "Mediocre", "Bad", "Good",
"Mediocre", "Bad", "Good", "Mediocre", "Bad", "Good", "Mediocre",
"Bad", "Good", "Mediocre", "Bad", "Good", "Mediocre", "Bad",
"Good", "Mediocre", "Bad", "Good", "Mediocre", "Bad", "Good",
"Mediocre", "Bad", "Good", "Mediocre", "Bad", "Good", "Mediocre",
"Bad", "Good", "Mediocre", "Bad", "Good", "Mediocre", "Bad",
"Good", "Mediocre", "Bad", "Good", "Mediocre", "Bad", "Good",
"Mediocre", "Bad"), Percentage = c(36.85, 36.88, 46.28, 60.28,
45.3, 36.42, 70.44, 37.39, 34.17, 58.23, 48.77, 34.99, 67.53,
33.46, 32.01, 35.96, 35.33, 62.71, 46.84, 32.92, 42.24, 83.21,
26.67, 16.11, 65.91, 23.94, 46.15, 81.83, 23.86, 27.31, 74.32,
35.09, 33.59, 71.91, 30.92, 28.17, 62.84, 33.06, 27.1, 63.05,
44.81, 26.15, 76.68, 35.99, 23.33, 49.19, 34.58, 42.23, 55.21,
35.37, 34.42, 64.48, 36.53, 33.99, 70.81, 27.1, 31.09, 51.36,
43, 37.65, 64.57, 34.37, 29.06, 35.55, 28.44, 56.01, 56.84, 33.36,
38.8, 79.74, 35.74, 22.52, 66.86, 29.99, 44.15, 89.57, 27.79,
14.64, 82.49, 27.37, 27.14, 75.92, 33.39, 18.69, 69.8, 34.69,
29.51, 75.2, 42.63, 29.17, 90.33, 36.72, 13.95, 52.73, 30, 38.27,
68.14, 40.61, 33.25, 66.2, 33.99, 31.81, 80.38, 26.48, 21.13,
50.5, 40.36, 37.14, 74.17, 31.78, 29.04, 44.91, 35.24, 43.85,
69.23, 35.44, 32.33, 86.44, 24.11, 17.46, 69.69, 33.06, 40.25,
86.37, 21.21, 21.42, 80.11, 35.57, 32.32, 77.2, 32.03, 19.77,
72.98, 28.08, 20.94, 70.81, 29.12, 24.07, 88.14, 22.31, 16.55,
67.49, 44.16, 32.35, 69.03, 39.45, 28.52, 71.97, 37.6, 25.43,
79.06, 38.4, 19.55, 68.94, 37.03, 30.03, 80.74, 30.59, 30.67,
49.07, 45.79, 47.14, 60.1, 27.55, 34.36, 88.54, 30.2, 20.26,
59.42, 22.98, 43.61, 86.84, 16.73, 14.43, 77.42, 22.07, 22.52,
78.85, 23.88, 17.28, 78.22, 39.57, 27.22, 80.17, 26.21, 20.63,
94.63, 28.66, 13.71, 65.86, 31.97, 32.16)), row.names = c(NA,
-192L), class = c("tbl_df", "tbl", "data.frame"))
Solution
To (vertically) merge cells they have to contain the same value, i.e. use the same label or value for the header cells in the first column if you want to merge them using merge_v.
To color the "group b" in green you have to target the 6th column, i.e. the column where "group b" starts.
library(tidyverse)
library(officer)
library(flextable)
header_labels <- gsub("\\-.+$", "", names(df))
names(header_labels) <- names(df)
# Use "Categories" as label to merge the cells
header_labels[["Category"]] <- "Categories"
custom_border <- officer::fp_border(color = "black", width = 1)
# Display the flextable with borders and color
ft1 <- ft %>%
flextable::border_outer(part = "all", border = custom_border) %>% # Add outer borders
flextable::border(i = NULL, j = 1, border.left = custom_border) %>% # Add left border
flextable::border(i = NULL, j = ncol(df), border.right = custom_border) %>% # Add right border
flextable::hline(border = custom_border, part = "body") %>% # Add horizontal lines between rows
flextable::vline(border = custom_border, part = "all") %>%
flextable::align(j = 2:ncol(df), align = "center", part = "all") %>%
flextable::set_header_labels(ft,
values = header_labels
) %>%
add_header_row(values = c(
"Categories",
"group a",
"group b",
"group c"
), colwidths = c(1, 4, 4, 4)) %>%
flextable::bold(part = "header") %>% # Make the header text bold
flextable::width(j = 1, width = 2) %>%
flextable::width(j = c(2, 5, 8), width = 1)
ft1 |>
merge_v(j = 1, part = "header") |>
color(
i = 1, j = 6,
color = "darkgreen", part = "header"
)
- Working with weekdays in number format in R lubriday::wday
- Print glmmTMB conditional and zero-inflated model side-by-side with modelsummary
- How to set factor levels in a rast stack, using terra, when different levels exist in each layer
- How to return the col type of a R tibble in compact string representation?
- Cropping in sf: Flat space vs. spherical geometry, and sf_use_s2()
- Convert .csv.gz to GeoJSON with R
- Is there a way to weight geom_smooth by different quantiles?
- How do I open a new tab or window in selenider in R?
- Wrong variable comparison result when performing data.table merge of two table with duplicated keys
- Setting the "binwidth" in ggplot histogram with POSIXct / datetime values (geom_histogram)
- lapply returns the same value for every data frame element
- Pivot wider in R with 2 variables to names_from
- Replicate Index/Match in Tidyverse with date range and multiple criteria
- Is there a minimum vertical spacing for legend keys?
- Passing variables though hierarchy of functions in Rstudio
- Need the word Delta spelled out in an R plot title but keeps converting to delta symbol
- runnig ANOVA for selected variables only with grep() in r
- how to strip certain patterns in column headers and rename them all in one go
- Reformat data to summarize and collapse rows into simple table
- Looping through a data frame whilst grouping rows
- How to mark rows with NAs which were replaced by na.approx()?
- R parLapply not parallel
- Adding a border to the column label row in gt
- Replace all instances of a character, but only within matching strings
- Set the size of ggsave exactly
- Unexpected behaviour of "formula" with "data.table" in R
- Is there an R function to escape a string for regex characters
- Why R function doesn't use variable from parent parent environment?
- Rename mutiple variables based on a vector
- Using a gsub to remove a special character that isn't always in the same place