crosstable sex with flextable and tabulator

Question

I can't figure out how to solve this easily. I would like to create a crosstable of my value crossed by sex (and age later adding the right column)

My table below :

x>
country sex answer value
SCO ALL A 50
SCO M A 52 
SCO F A 48
SCO ALL B 71 
SCO M B 77 
SCO F B 65
WAL ALL A 45
WAL M A 48
WAL F A 43
WAL ALL B 71
WAL M B 79
WAL F B 63

I would like to cross table to get something like this where i would later add an age variable of whatever

I've tried something like this using flextable::tabulator

tabulator(
    x = x, rows = c("answer", "country"),
    columns = c("sex"),
  ) |>
    as_flextable()

and I encounter this error Error in [.data.table(dat, , .SD, .SDcols = columns) : Some items of .SDcols are not column names: [.tab_columns] I've seen the crosstable function to but I can't figure out how to use it to this purpose

Answer 1

I am not sure if it will answer your need, tabulator produces regular crosstable but here it seems you want to mix different columns, not to spread them along factors.

library(flextable)
x <- structure(list(country = c("SCO", "SCO", "SCO", "SCO", "SCO", "SCO", "WAL", "WAL", "WAL", "WAL", "WAL", "WAL"), sex = c("ALL", "M", "F", "ALL", "M", "F", "ALL", "M", "F", "ALL", "M", "F"), answer = c("A", "A", "A", "B", "B", "B", "A", "A", "A", "B", "B", "B"), value = c(50, 52, 48, 71, 77, 65, 45, 48, 43, 71, 79, 63)), class = c("tbl_df", "tbl", "data.frame"), row.names = c(NA, -12L))
x$age_value <- rpois(n = nrow(x), lambda = 35)
tabulator(
  x = x, 
  rows = c("answer", "country"),
  columns = c("sex"),
  label_value = as_paragraph(value),
  label_age = as_paragraph(as_chunk(age_value))
) |>
  as_flextable()]

Answer 2

library(tidyverse)
library(knitr)
library(kableextra)
df.wide <- pivot_wider(df, names_from = sex)
df.wide %>%  
  arrange(answer) %>%
  select(-"answer") %>%
  knitr::kable("html", booktabs = TRUE) %>%
  column_spec(2:4, width_min = "2em")%>%
  add_header_above(c(" " = 1, "Sex" = 3)) %>%
  pack_rows(index = table(df.wide$answer))