Format data to create neat table in R markdown using kable

Question

I want to create a neat table in R markdown using the kable package, but my data need to be transformed.

My data look like this:

category <- c('population', 'population', 'sample', 'sample', 'population', 'population', 'sample', 'sample')
gender <- c('female', 'male', 'female', 'male', 'female', 'male', 'female', 'male')
n <- c(12,20,14,14,11,21,13,15)
frequency <- c(0.375, 0.625, 0.5, 0.5, 0.34375, 0.65625, 0.4642857, 0.5357143)
cohort <- c('one', 'one', 'one', 'one', 'two', 'two', 'two', 'two')

df <- data.frame(category, gender, n, frequency, cohort)

I would like to create a table in R markdown that looks like this (but I welcome other suggestions):

Any ideas on how to solve this? I appreciate any help!

Answer 1

As suggested by @rjen, the kableExtra package might work for you. Below is a working example.

First, would put into wide format, and order the columns consistent with the desired table. Then, set alignment to c (center) and hide the column names. Using add_header_above will give you the 3-layered header. The numbers used indicates the width/span of the header with respect to the columns underneath.

library(tidyverse)
library(kableExtra)

df %>%
  pivot_wider(id_cols = cohort, names_from = c(category, gender), values_from = c(frequency, n)) %>%
  select(cohort, frequency_population_male, frequency_sample_male, frequency_population_female, frequency_sample_female,
         n_population_male, n_sample_male, n_population_female, n_sample_female) %>%
  mutate_if(is.numeric, format, digits=2) %>%
  kable(align = rep('c', 9), col.names = NULL) %>%
  kable_classic() %>%
  add_header_above(c("Cohort", rep(c("Population", "Sample"), 4))) %>%
  add_header_above(c(" ", "Male" = 2, "Female" = 2, "Male" = 2, "Female" = 2)) %>%
  add_header_above(c(" ", "Frequency" = 4, "N" = 4))

Output