rdplyrr-markdownxtablegt

custom table for Rmarkdown pdf

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 .

enter image description here

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

  1. Have a control on where I can place the variable column and its corresponding values in a format like the example table snapshot above.
  2. 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"))
Solution

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

    enter image description here

    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!