pivot wider - create two levels of headers for better readability

Question

There are six regression models. I used pivot wider but it is difficult to read. Can I use two level of headers -

first level - regression model
second level - estimate, tstat

library(dplyr)

regression <- c(rep("A", 3), rep("B", 3), rep("C", 3), rep("D", 3), rep("E", 3), rep("F", 3))
attribute <- rep(c("b0", "b1", "b2"), 6)
estimate <- round(runif(n = 18, min = 0, max = 10), 2)
tstat <- round(runif(n = 18, min = 0, max = 10), 2)

# tibble
tbl <- tibble(regression, attribute, estimate, tstat)

# pivot wider
tbl <- tbl %>% 
  pivot_wider(names_from = regression,
              values_from = c("estimate", "tstat"))

Where are you trying to use this output? Putting header text into your numeric columns will turn those columns into characters, which will change how you're able to work with them. Or is it for output in e.g. an Rmarkdown document, in which case you'd want to use a package that's specific to formatting tables for display? — camille, Oct 30 '21 at 19:16

akrun · Accepted Answer · 2021-10-30T21:21:06.263

3

One option is separate_header from ftExtra

library(ftExtra)
library(dplyr)
library(tidyr)
library(stringr)
tbl %>% 
     pivot_wider(names_from = regression,
                 values_from = c("estimate", "tstat")) %>% 
     rename_with(~ str_replace(., "(.*)_(.*)", "\\2_\\1"), -1) %>%
     as_flextable() %>%
     separate_header()

-output

Or may use span_header

library(flextable)
tbl %>%  
  pivot_wider(names_from = regression,
              values_from = c("estimate", "tstat")) %>%   
  rename_with(~ str_replace(., "(.*)_(.*)", "\\2_\\1"), -1) %>% 
  select(attribute, order(str_remove(names(.)[-1], "_.*")) + 1) %>% 
  as_flextable() %>% 
  span_header()  %>% 
  align(align = "center", part = "all")

-output

If we need to make some column bold,

tbl %>%  
    pivot_wider(names_from = regression,
                values_from = c("estimate", "tstat")) %>%   
    rename_with(~ str_replace(., "(.*)_(.*)", "\\2_\\1"), -1) %>% 
    mutate(across(ends_with('tstat'),  ~sprintf('**%.2f**', .))) %>% 
    select(attribute, order(str_remove(names(.)[-1], "_.*")) + 1) %>% 
    as_flextable() %>% 
    span_header()  %>% 
    align(align = "center", part = "all") %>% 
    colformat_md()

-output

edited Oct 30 '21 at 21:21

answered Oct 30 '21 at 18:27

akrun

874,273
37
540
662

thanks. is it possiblr group regression models together (for ex - As, Bs, ...) – SiH Oct 30 '21 at 19:18
1

Thnaks, thats almost correct, the upper level headers are not accurate (A should span from estimate to tstat) – SiH Oct 30 '21 at 19:43
Thnaks, thats almost correct, the upper level headers are not accurate (A should span from estimate to tstat and should be in the middle) – SiH Oct 30 '21 at 19:49
@SiD that just needs an `align` from `flextable`. Updated – akrun Oct 30 '21 at 19:52
1

Thanks you very much – SiH Oct 30 '21 at 19:53
@akrun: How did you handle duplicate column names. I tried 1 hour to solve this with flextable? – TarJae Oct 30 '21 at 20:09
1

@TarJae there was no duplicate column names per se. if you check the output until the `select` step. The `span_header` or `separate_header` does the split at the `_` to split the column names. The columns are ordered so that all columns that start with 'A' are adjacent, followed by 'B' columns, etc and then we apply the span_header. It is just like `head(iris) %>% as_flextable() %>% span_header() %>% align(align = "center", part = "all")` – akrun Oct 30 '21 at 20:11
1

Fantastic. Thanks master! – TarJae Oct 30 '21 at 20:13
@akrun can i write the final results in csv file. It seems that it is image – SiH Oct 30 '21 at 20:43
@SiD That is why i said in the comments earlier that data.frame/tibble doesn't allow multiple headers. You can save as an image. if the output object is `out` `save_as_image(out, path = "out.png", webshot = "webshot2")` – akrun Oct 30 '21 at 20:48
okay, got it thanks – SiH Oct 30 '21 at 20:50
@SiD You may also check [here](https://stackoverflow.com/questions/37866714/most-efficient-way-to-export-contingency-table-from-r-to-excel) for alternative ways – akrun Oct 30 '21 at 20:51
@akrun just wondering, is it possible in R to bold estimate values in table , based on tstat value so it is more readable – SiH Oct 30 '21 at 21:16
@SiD try the update – akrun Oct 30 '21 at 21:21
@SiD if you have more questions, please consider to post as a new question. thanks – akrun Oct 30 '21 at 21:25

pivot wider - create two levels of headers for better readability

1 Answers1

Linked