Much like this poster, I am trying to make a table in r where I am grouping by levels of a factor. I am happy to do this in gt or kabbleExtra or any other package which makes a graph like this possible.
I would like to take the graph from the first post one step further to add a second set of groupings, almost exactly like what was done here:
I followed the suggestions in the other post but this left me with a table that was flipped, and the rows weren't segmented correctly (I had columns for male and female, and three rows for each variable for age group, but the variables had the same name so it was difficult to tell which was for which age group).
#Desired output:
# age_y age_m age_o
# Count Percent | Count Percent | Count Percent
#VARIABLE 1
# Male 45 45% 76 56% 43 89%
# Female 23 65% 11 88% 46 91%
#VARIABLE 2
# Male 45 45% 76 56% 43 89%
# Female 23 65% 11 88% 46 91%
#VARIABLE 3
# Male 45 45% 76 56% 43 89%
# Female 23 65% 11 88% 46 91%
reproducible example
data <- data.frame(ID=c("Mary","Mary","Mary","Jane","Jane","Jane","John","John","John"),
sex=c("F","F","F","F","F","F","M","M","M"),
age=c(1,1,1,3,3,3,2,2,2),
Variable = c("VARIABLE 1", "VARIABLE 2", "VARIABLE 3"),
Count = c(45, 76, 43),
Percent = c(0.45, 0.56, 0.89))
I have tried the following syntax, but I have not been able to segment the data in the table by two separate factor conditions. Thank you in advance for any help.
# Helper to put the columns in the right order
cols_order <- unlist(lapply(c("M", "F"), function(x) paste(x, c("Count", "Percent"), sep = "_")))
data_wide <- data %>%
tidyr::pivot_wider(names_from = "sex", values_from = c("Count", "Percent"), names_glue = "{sex}_{.value}") %>%
# Reorder columns
select(all_of(c("Variable", cols_order)))
data_wide %>%
gt(rowname_col = "Variable") %>%
tab_spanner_delim(delim = "_")
