CODEWITHSUNDEEP
rr-markdownkableextra
MartineJ
MartineJ

Reputation: 795

How to adjust labels in table created with R kableExtra::add_header_above?

How can I (or can I) adjust labels in a table created with kableExtra::add_header_above()? A small example:

library(knitr)
library(dplyr)
library(tidyr)
library(kableExtra)

df <- tibble(year = c(2017, 2018, 2017, 2018),
         major_item = "M",
         sub_item = c( "A", "A", "B", "B"),
         n = c( 1:4))
df %>%
  filter(sub_item == "A") %>%
  select(-sub_item) %>%
  spread(year,n) %>%
  # now join with the part for which sub_item == "B"
  left_join(df %>%
               filter(sub_item == "B") %>%
               select(-sub_item) %>%
               spread(year,n),
            by = c("major_item" = "major_item"))  %>%
  # now the nice header
  kable("latex", booktabs = T) %>%
  kable_styling() %>% 
  add_header_above(c(" " = 1, "A" = 2, "B" = 2))

The result is:

enter image description here

Of course the suffix .x appears because there are columns with the same name, which is not allowed in one set. What can I do to make the 2017.x and 2018.x the same as the other columns 2017 and 2018? Any tips on how to get the result table in less steps are also very welcome. Thanks!

Update 2019_09_26: Thanks to pivot_wider, I found an elegant way to structure the table, and thanks to iago it has the desired column names:

df %>%
# the table structure
pivot_wider(names_from = c(year, sub_item),
            values_from = n) %>%
# the nice headings
kable("latex", booktabs = T,
      col.names = c("", "2017", "2018", "2017", "2018")) %>%
kable_styling() %>% 
add_header_above(c(" " = 1, "A" = 2, "B" = 2))

Upvotes: 3

Views: 1308

Answers (2)

Emmanuel Hamel
Emmanuel Hamel

Reputation: 2213

Here is another approach that can be considered :

library(knitr)
library(dplyr)
library(tidyr)
library(kableExtra)
library(stringr)

df <- tibble(year = c(2017, 2018, 2017, 2018),
         major_item = "M",
         sub_item = c( "A", "A", "B", "B"),
         n = c( 1:4))

tab <- df %>%
  filter(sub_item == "A") %>%
  select(-sub_item) %>%
  spread(year,n) %>%
  # now join with the part for which sub_item == "B"
  left_join(df %>%
               filter(sub_item == "B") %>%
               select(-sub_item) %>%
               spread(year,n),
            by = c("major_item" = "major_item"))  %>%
  # now the nice header
  kable("latex", booktabs = T) %>%
  kable_styling() %>% 
  add_header_above(c(" " = 1, "A" = 2, "B" = 2))

tab[1] <- stringr::str_replace_all(tab[1], "(\\d{4})(\\.x)", "\\1")
tab[1] <- stringr::str_replace_all(tab[1], "(\\d{4})(\\.y)", "\\1")
tab

Upvotes: 0

iago
iago

Reputation: 3256

I would do:

library(knitr)
library(dplyr)
library(tidyr)
library(kableExtra)

df <- tibble(year = c(2017, 2018, 2017, 2018),
         major_item = "M",
         sub_item = c( "A", "A", "B", "B"),
         n = c( 1:4))

df %>%
  filter(sub_item == "A") %>%
  select(-sub_item) %>%
  spread(year,n) %>%
  # now join with the part for which sub_item == "B"
  left_join(df %>%
               filter(sub_item == "B") %>%
               select(-sub_item) %>%
               spread(year,n),
            by = c("major_item" = "major_item"))  %>%
  # now the nice header
  kable("latex", booktabs = T, col.names = c("major_item","2017","2018","2017","2018")) %>%
  kable_styling() %>% 
  add_header_above(c(" " = 1, "A" = 2, "B" = 2))

Upvotes: 2

Related Questions