flextable group by column

Question

I am producing reports through Rmarkdown that need to be able to be rendered to Word. I am hoping to be able to reporduce this table format:

I have tried using flextable to do this without any real headway. I've read through the flextable site and have found a way to use groups for merging rows, but not to group columns. The as_grouped_data() function can create the groups beforehand, but can I use these groups for column headings?

Small example of my data:

df = structure(list(Athlete = structure(c(1L, 1L, 1L, 2L, 2L, 2L, 
1L, 1L, 1L, 2L, 2L, 2L), .Label = c("Athlete 1", "Athlete 2"), class = "factor"), 
    Time = structure(c(1L, 2L, 3L, 1L, 2L, 3L, 1L, 2L, 3L, 1L, 
    2L, 3L), .Label = c("1", "2", "3"), class = "factor"), Measure = structure(c(1L, 
    1L, 1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 2L, 2L), .Label = c("Distance", 
    "Speed"), class = "factor"), Value = c(4.02, 11.5, 19.82, 
    3.03, 9.67, 17.9, 6.5, 8.08, 8.47, 5.3, 7.64, 8.67)), row.names = c(NA, 
-12L), class = "data.frame")

# Create table
myft = flextable(df) 
# Merge athlete coloumn
myft = merge_v(myft, j=c("Time", "Athlete"))
myft

I am stuck here and unsure how to swivel these groups from row headings to column headings.

user_3460670 · Answer

To create the format you desire for your table, you probably want the data "wide." This will give you 2 columns for your 2 athletes. This example uses pivot_wider from tidyr.

After arranging by Time order, you use your merge_v from flextable. Added fix_border_issues to fix bottom table border after merge.

library(flextable)
library(tidyverse)

myft <- df %>%
  pivot_wider(id_cols = c(Time, Measure), names_from = Athlete, values_from = Value) %>%
  arrange(Time) %>%
  flextable()

# Merge Time column vertically
myft = merge_v(myft, j="Time")
myft = fix_border_issues(myft, part = "all")
myft

Output

flextable group by column

1 Answers