How to merge column names with data frame (or kable) in R?

Question

Newbie in R here. In Excel, if you click and drag to select multiple cells, you can click on "merge cells" to convert multiple cells into one. Similar mindset, I want to do this in R.

Take this dataframe for this example:

library(kableExtra)
library(magrittr)
library(knitr)
We_Love_Colours <- data.frame(
  Name = c("Colours", "", ""),
  G = c("green","green","green"),
  B = c("blue", "blue", "blue"),
  R = c("red", "red", "red"),
  Y = c("yellow", "yellow", "yellow"),
  P = c("purple", "purple", "purple"),
  C = c("cyan", "cyan", "cyan")
) 

kable(We_Love_Colours, format = "html", escape = FALSE, row.names = FALSE, caption = "Table 1.0: Rainbow?") %>%
  kable_styling(
    bootstrap_options = c("striped"),
    full_width = FALSE,
    position = "center"
  )

And the output should look something like the image I'm uploading here.

I would like to merge the column names "G", "B", "R", "Y", "P", "C" into a single cell and write "Colours". How can I do this?

Just like with everything, I tried to get help from ChatGPT and it suggested to add this part:

kable(We_Love_Colours, format = "html", escape = FALSE, row.names = FALSE, caption = "Table 1.0: Rainbow?") %>%
  add_header_above(c(" " = 1, "Colours" = 6)) %>%
  kable_styling(
    bootstrap_options = c("striped"),
    full_width = FALSE,
    position = "center"

However, that just generated a new row above the column names and "Colours" hovered over the column names. I was seeking to replace those column names with a single column name.

Answer 1

One option would be to remove the column names using col.names=NULL as suggested here, then use add_header_above to add your merged header:

library(kableExtra)
library(magrittr)
library(knitr)

kable(We_Love_Colours,
  format = "html", escape = FALSE,
  row.names = FALSE,
  col.names = NULL,
  caption = "Table 1.0: Rainbow?"
) %>%
  kable_styling(
    bootstrap_options = c("striped"),
    full_width = FALSE,
    position = "center"
  ) %>%
  add_header_above(
    c(" " = 1, "Colours" = 6),
    line = FALSE
  )