custom table for Rmarkdown pdf

Question

I have a table as below (dput at the end)

# A tibble: 35 x 5
# Groups:   year [1]
   school_id class_id  year variable                value
       <dbl> <chr>    <dbl> <chr>                   <dbl>
 1       123 1         2020 sex_numerator_male        137
 2       123 1         2020 sex_numerator_female       67
 3       123 1         2020 sex_denominator_male   156986
 4       123 1         2020 sex_denominator_female 173055
 5       123 1         2020 age_numerator_18_34        76
 6       123 1         2020 age_numerator_35_54        97
 7       123 1         2020 age_numerator_55           31
 8       123 1         2020 age_denominator_18_34   98662
 9       123 1         2020 age_denominator_35_54  101261
10       123 1         2020 age_denominator_55     130118

What I'm trying to do is to make a custom table something like this below. I could not solve it in R hence pasting a screen shot. The corresponding value of the variable name should be displayed. For easy understanding, just showing the variable name in the table image .

I'm using the GT package to organize the data into the desired table. Could not succeed. Along with sex , age, I have other row values as well. What I'm trying is to

Have a control on where I can place the variable column and its corresponding values in a format like the example table snapshot above.
I'm grouping the table by 3 columns, School_id, class_id and year. So, that I can generate table for each group separately.

Appreciate if any solution is suggested .

structure(list(school_id = c(123, 123, 123, 123, 123, 123, 123, 
123, 123, 123, 123, 123, 123, 123, 123, 123, 123, 123, 123, 123, 
123, 123, 123, 123, 123, 123, 123, 123, 123, 123, 123, 123, 123, 
123, 123), class_id = c("1", "1", "1", "1", "1", "1", "1", "1", 
"1", "1", "1", "1", "1", "1", "1", "1", "1", "1", "1", "1", "1", 
"1", "1", "1", "1", "1", "1", "1", "1", "1", "1", "1", "1", "1", 
"1"), year = c(2020, 2020, 2020, 2020, 2020, 2020, 2020, 2020, 
2020, 2020, 2020, 2020, 2020, 2020, 2020, 2020, 2020, 2020, 2020, 
2020, 2020, 2020, 2020, 2020, 2020, 2020, 2020, 2020, 2020, 2020, 
2020, 2020, 2020, 2020, 2020), variable = c("sex_numerator_male", 
"sex_numerator_female", "sex_denominator_male", "sex_denominator_female", 
"age_numerator_18_34", "age_numerator_35_54", "age_numerator_55", 
"age_denominator_18_34", "age_denominator_35_54", "age_denominator_55", 
"all_sex_numerator_male_sum", "all_sex_denominator_male_sum", 
"all_sex_numerator_female_sum", "all_sex_denominator_female_sum", 
"all_age_numerator_18_34_sum", "all_age_denominator_18_34_sum", 
"all_age_numerator_35_54_sum", "all_age_denominator_35_54_sum", 
"all_age_numerator_55_sum", "all_age_denominator_55_sum", "total_num_sex_m", 
"total_num_sex_f", "total_num_age_18_34", "total_num_age_35_54", 
"total_num_age_55", "county_OD_death_sex_m", "county_OD_death_sex_f", 
"county_OD_death_age_18_34", "county_OD_death_age_35_54", "county_OD_death_age_55", 
"allhcs_OD_death_sex_m", "allhcs_OD_death_sex_f", "allhcs_OD_death_age_18_34", 
"allhcs_OD_death_age_35_54", "allhcs_OD_death_age_55"), value = c(137, 
67, 156986, 173055, 76, 97, 31, 98662, 101261, 130118, 1441, 
1908231, NA, 2073091, 687, 1237334, 996, 1228864, NA, 1515124, 
47.5656054853791, 52.4343945146209, 29.8938616717317, 30.6813395911417, 
39.4247987371266, 87.2689284394787, 38.7160151396955, 77.0306703695445, 
95.7920620969574, 23.8245285048956, 75.5149664794252, NA, 55.5225993951512, 
81.0504661215562, NA)), row.names = c(NA, -35L), groups = structure(list(
    year = 2020, .rows = structure(list(1:35), ptype = integer(0), class = c("vctrs_list_of", 
    "vctrs_vctr", "list"))), row.names = c(NA, -1L), class = c("tbl_df", 
"tbl", "data.frame"), .drop = TRUE), class = c("grouped_df", 
"tbl_df", "tbl", "data.frame"))

xtable should do this, there is plenty support for customising it to do what you want, could you try producing an xtable attempt that we can help fix? — pluke, Aug 11 '21 at 22:08
@pluke can you share the xtable documentation, I can try. I thought gt package is more user friendly — user5249203, Aug 12 '21 at 19:45
https://cran.r-project.org/web/packages/xtable/vignettes/xtableGallery.pdf — pluke, Aug 12 '21 at 21:02
I'm not too familiar with the xtable package. I personally find kableExtra to easier to work with. — Brandon Bertelsen, Aug 14 '21 at 17:15

score 2 · Accepted Answer · answered Aug 14 '21 at 18:00

Here's an example you can build on top of:

library(dplyr)
library(kableExtra)

collapse_rows_dt <- data.frame(
  C1 = c(rep("Sex", 2), rep("Age", 5)),
  C2 = c("m", "f", "18-34", "35-44", "45-54", "55-64", "65+"),
  C3 = 1:7,
  C4 = sample(0:100, 7, replace = TRUE))

names(collapse_rows_dt) <- c("", "", "Total Number", "County")

kbl(collapse_rows_dt, align = "c") %>%
  # set background on first two columns
  column_spec(1:2, bold = T, background = "#999", width = "4em") %>% 
  # set background and font color on header row
  row_spec(0, bold = T, color = "#000", background = "#999") %>% 
  # add your header
  add_header_above(c("Class 1: Results for 2020" = 4)) %>% 
  # collapse the rows so they look "stacked"
  collapse_rows(columns = 1:2, valign = "top") %>% 
  # set a theme
  kable_classic(full_width = T)

This should be enough to get you started. One issue is that kableExtra does not support vertical borders in HTML, only in LaTeX. But if you can avoid working with LaTeX, you'll be happier for it. Each line of the kableExtra call is separated with a comment that identifies what the next line does. There are significantly more options in kableExtra.

Here's a vignette that should have everything you need:

https://cran.r-project.org/web/packages/kableExtra/vignettes/awesome_table_in_html.html#Column__Row_Specification

PS: Avoid LaTeX

PPS: Avoid LaTeX!

custom table for Rmarkdown pdf

1 Answers1

Linked