Is `gt` the pacakge I want for a nested table in long format?

[pdeffebach]

I have a table that looks as follows:

$> all_specs                                                                                            
# A tibble: 24 x 8
   term         estimate  std.error statistic  p.value outcome  type  fe        
   <chr>           <dbl>      <dbl>     <dbl>    <dbl> <chr>    <chr> <chr>     
 1 ratio_post  0.00144   0.000971        1.48 1.39e- 1 lambda_W data  all       
 2 ratio_post  0.00912   0.000599       15.2  2.13e-52 lambda_W sim   all       
 3 ratio_post -0.00670   0.000761       -8.81 1.26e-18 lambda_W data  stay, home
 4 ratio_post  0.000981  0.000839        1.17 2.42e- 1 lambda_W sim   stay, home
 5 ratio_post -0.00670   0.000761       -8.81 1.26e-18 lambda_W home  home      
 6 ratio_post  0.000981  0.000839        1.17 2.42e- 1 lambda_W sim   home      
 7 ratio_post  0.0000121 0.0000116       1.05 2.93e- 1 lambda_T data  all       
 8 ratio_post  0.00100   0.000917        1.10 2.73e- 1 lambda_T sim   all       
 9 ratio_post -0.0000159 0.00000863     -1.84 6.57e- 2 lambda_T data  stay, home
10 ratio_post  0.000976  0.000651        1.50 1.34e- 1 lambda_T sim   stay, home

I was hoping to use some sort of ggplot-esque tool to get this data into a format that would look something like the left hand side being the "type" column, then below that I subset by "sim" and "data". Columns show the betas and ses for different specification.

It's hard for me to make a prototype table showing this, but maybe you get the idea.

I thought gt would have this functionality but now I'm not so sure. Can someone help me understand what tool I could use for this?

EDIT: Here is a picture I drew of the table I would like to programmatically generate

[pdeffebach]

It looks like I can get most of the way there with the following:

t = all_specs %>%
  select(-term, -statistic, -p.value) %>%
  gt(
    rowname_col = "type",
    groupname_col = "outcome",
  )

Unfortunately, it doesn't look like you can use grouping functionality to make spanned columns. Is this correct?

[hughjonesd]

I think tidyr may be what you want. It will help reshaping your data. pivot_wider() can be used to pivot the estimate and std.error columns into separate columns for each value of fe. Then you can use gt or similar tools to format the table for display.

[pdeffebach]

Yes, this is essentially what I did. I used pivot_wider and some clever renaming of columns to make it work with gt.

t = all_specs %>%
  select(-term, -statistic, -p.value) %>%
  rename(
    Beta = "estimate",
    SE = "std.error"
  ) %>%
  pivot_wider(values_from = c("Beta", "SE"), names_from = "fe") 
  
reverse_names = function(x, delim){
  out = c()
  for(xi in x){
    parts = str_split(xi, delim)[[1]]
    newname = paste(rev(parts), collapse = delim)
    out = c(out, newname)
  }

  return(out)
}

names(t) = reverse_names(names(t), "_")
t = t[,order(names(t))]

g = t %>% gt(
    rowname_col = "type",
    groupname_col = "outcome",
  ) %>%
  tab_header(title = "Outcome: Ratio by post-subway") %>%
  
  fmt_number(
    columns = matches("Beta"), 
    decimals = 4
  ) %>%
  fmt_number(
    columns = matches("SE"), 
    decimals = 4
  ) %>%
  tab_spanner_delim(delim = "_", gather = TRUE)

Note that the pivot_wider call has the names in an order that I dont want, since gt's tab_spanner_delim has the first term in the separated name be the upper column spanner. This was a bit annoying.

Second, I don't think the gather option is working properly, notice that I have to sort the names in the data frame t before calling gt. I filed an issue about this here.

Overally I liked using gt but the way you assign column spanners based off of the names seems "hacky" to me. I would love if it could use grammar-of-graphics conventions for columns in addition to rows.

Second, have some quibbles with the latex output. It would be great if it were a short table by default and had the option of being a fragment, i.e. starting at \begin{tabular} instead of \begin{table} (well, \begin{longtable}). It's nice to be able to write your custom caption and label in the latex document and \input the table itself inside of your \begin{table} environment.

Let me know if I should file issues for these feature requests or if they are already being worked on.