Merging column header in latex table created by kable in Rmarkdown

Question

I have created a latex table as below using kable in Rmarkdown:

---
output: pdf_document
header-includes:
  - \usepackage{xcolor}
---

```{r, message=FALSE, warning=FALSE, echo=FALSE}
library(kableExtra)
library(tidyr)
library(dplyr)

data(iris)
iris %>% 
  as_tibble %>%
  gather(.,key = variable,value = value,-Species) %>%
  group_by(Species,variable) %>%
  summarise(value=mean(value)) %>%
  ungroup %>%
  spread(.,key = variable,value = value) %>%
  mutate(`Percentage Change`=`Petal.Length`/`Petal.Width`*100) %>%
        kable(.,format='latex',
        align='c',linesep='',
        booktabs=TRUE,escape=FALSE) %>%
  add_header_above(.,c(' '=1,'Parts'=4,' '=1),
                   escape = FALSE) %>%
  kable_styling(latex_options = c('striped','HOLD_position','scale_down'))
```

I would like to have the column header "Species" and "Percentage Change" merged with the empty space above them respectively, so that Species can be placed in the middle of the two header rows, while Percentage Change (Petal Length/ Petal Width) can occupy two rows, rather than having a empty row above, and prevent other column to have an empty row below.

Wonder if it can be modified in kable preferably, latex "hack" suggestion is also welcome.

Thanks!

Ossetic

Answer 1

I think for this latex 'hack' solution is much cleaner. In kable also this can be done but that would require changing the data frame (convert column names to row) so that collapse_rows can be used. Anyway, here's the latex way out:

The code that you gave in your questions does not give the column name as in the pdf snapshot. So I edited the code first to get that table:

---
output: 
  pdf_document:
    keep_tex: true
header-includes:
  - \usepackage{xcolor}
---

```{r, message=FALSE, warning=FALSE, echo=FALSE}
library(kableExtra)
library(tidyr)
library(dplyr)

data(iris)
iris %>% 
  as_tibble %>%
  gather(.,key = variable,value = value,-Species) %>%
  group_by(Species,variable) %>%
  summarise(value=mean(value)) %>%
  ungroup %>%
  spread(.,key = variable,value = value) %>%
  mutate('Percentage Change\n(Petal length/ Petal width)'=`Petal.Length`/`Petal.Width`*100) %>%
  kable(format='latex',align='c',linesep='',booktabs=TRUE,escape=FALSE,
        col.names = linebreak(colnames(.),align = 'c')) %>%
  add_header_above(.,c(' '=1,'Parts'=4,' '=1),escape = FALSE) %>%
  collapse_rows(columns = c(1,6),valign = 'middle')%>%
  kable_styling(latex_options = c('striped','HOLD_position','scale_down'))
```

This gives this:

Note two things in above code:

1. keep_tex: true: this retains the .tex file generated and can be used to edit.
2. Use of linebreaks to ensure that the entire column name for last column is not in one line.

Now we make small changes in latex output. In code below the commented out line is the original code generated by kable. This is replaced by the new lines just below the commented out line as indicated.

\begin{table}[H]
\centering
\resizebox{\linewidth}{!}{
\begin{tabular}{cccccc}
\toprule
% \multicolumn{1}{c}{ } & \multicolumn{4}{c}{Parts} & \multicolumn{1}{c}{ } \\
\multirow{2}{*}{Species} & \multicolumn{4}{c}{Parts} & \multirow{2}{*}{\makecell[c]{Percentage Change\\(Petal length/ Petal width)}} \\ % replaced line
\cmidrule(l{3pt}r{3pt}){2-5}
% Species & Petal.Length & Petal.Width & Sepal.Length & Sepal.Width & \makecell[c]{Percentage Change\\(Petal length/ Petal width)}\\
& Petal.Length & Petal.Width & Sepal.Length & Sepal.Width &\\ % replaced line
\midrule
\cellcolor{gray!6}{setosa} & \cellcolor{gray!6}{1.462} & \cellcolor{gray!6}{0.246} & \cellcolor{gray!6}{5.006} & \cellcolor{gray!6}{3.428} & \cellcolor{gray!6}{594.3089}\\
\cmidrule{1-6}
versicolor & 4.260 & 1.326 & 5.936 & 2.770 & 321.2670\\
\cmidrule{1-6}
\cellcolor{gray!6}{virginica} & \cellcolor{gray!6}{5.552} & \cellcolor{gray!6}{2.026} & \cellcolor{gray!6}{6.588} & \cellcolor{gray!6}{2.974} & \cellcolor{gray!6}{274.0375}\\
\bottomrule
\end{tabular}}
\end{table}

This gives the following output:

Asher

Thanks! It is work but under one condition that column_spec cannot be used when create the table via kable, column_spec seems to prevent using the code makecell, is there anyway to give the same result with column_spec being used? Thanks again! – Ossetic

Can you please give some more details about for what purpose you are using column_spec? – Asher

makecell is only to break column name for last column in two lines. There are alternative ways for handling this. – Asher

thanks for the reply Dayne, I will present a case on coming monday – Ossetic

Hey Dayne, the column_spec seems fixed, maybe it's just my mistake previously when trying to use column_spec with your solution incorporated, thanks again! – Ossetic

great! glad i could be of help. – Asher