Repeating column labels for groups (a la "Freeze Panes" on Excel)

[gitmoneyMSBA]

---

Thanks so much for the development and support of this package. It's the best table package we have on R and it renders beautifully to web pages and reports.

I'm often using it for larger tables split into two groups (>10 observations for each group), so the n and p are pretty close (somewhere between 10 and 15 variables).

Excel has a freeze panes option that allows you to scroll down a long table/grouped table and not lose track of which variable you're tracking.

I'm not sure if there's functionality to mimic that in grouped tables at the moment (hopefully there is and I've just missed this!), but it seems worthwhile for longer tables if there's not.

Imagine if country pops had many variables about population, and we could repeat the tab spanners/column names above both United States and Russian Federation). Example included has a wishful function called col_name_repeat.

library(gt)

country = countrypops %>% filter(country_name %in% c("United States", "Russian Federation"),  year >= 1985)

gt_tbl <-  gt(data = country ) %>%
    group_by(country_name) %>%
  tab_spanner(
    label = "Population statistics",
    columns = vars(population, 2:10)
  ) %>%
  tab_spanner(
    label = "Political system/info",
    columns = vars(11:19)
  ) %>%
  col_name_repeat(groups=c("United States", "Russian Federation")

[markbneal]

Does this relate to this issue? 412 DT handles this with extensions, what it calls FixedColumns and FixedHeaders, described here

[sumairshah2]

Is there anything like this available now

[mbsteel]

I, too, would be interested in this feature as I need to produce long reports on several variables for the 120 counties in Kentucky and would appreciate an update if it has or is being developed. Please and thank you!

[sumairshah2]

@mbsteel I figured out one way to manually do this. I do not think it has been developed to my knowledge.

[mbsteel]

@sumairshah2 I would appreciate seeing your method, if you don't mind. Otherwise, I might need to resort to creating multiple tables, one for each page.

[sumairshah2]

targetlogs.pdf

to confirm -- you are looking to do something similar to the report I attached? Repeat column names after every row group?

[mbsteel]

@sumairshah2 Yes, that would work for me.

[sumairshah2]

Shoot me an email - [email protected]

[gitmoneyMSBA]

Would you be willing to share your solution here? That’s exactly what I’m looking for in terms of a workaround.

[sumairshah2]

Yeah, let me write some example code and share it here. With my report I used above I have also started using https://gt.rstudio.com/reference/cols_hide.html to hide_columns. Makes it more aesthetic. You can see that in this report

[stevenbedrick]

I think this would be a super-useful feature, as well, and like the proposed col_name_repeat() syntax. @sumairshah2 , if you could post the code for your manual solution, I'd be really curious to see it!

[mbsteel]

In case it helps, I ended up using the kable package to produce repeating tables. But I do like some of the features in gt better and wish there was a way to use that package to the same effect.

[sumairshah2]

Hi @mbsteel @stevenbedrick @gitmoneyMSBA -- sorry for the late response. Paste this into R and let me know if it makes sense. Super hacky way of doing this and after seeing the logic you may find a cleaner way. Let me know if you have any questions or run into trouble. You should get this result below

library(janitor)
library(gt)
library(dplyr)

# Just used this as a built-in R data set
B <- Lahman::Batting %>% tail(5)
# Take the colnames
C <- colnames(B)

## you can probably figure this part out better but for now, rbind colnames * length(df)  
D <- as.data.frame(rbind(C,C,C,C,C),row.names = F,make.names = F) %>% janitor::row_to_names(1,remove_row = F)

## Replace our column df with a vector of real data from the original df
D$playerID <- B$playerID

# combine colnames and real data
final_df <- rbind(D,B)


## you can hide or not hide column names. 

final_df %>% arrange(desc(yearID)) %>% gt(groupname_col ="playerID")%>% tab_header(title = md("**Advisees**"),)  %>% tab_options(heading.background.color = "#EFFBFC",stub.border.style = "dashed",stub.border.color = "#989898",stub.border.width = "1px",summary_row.border.color = "#989898",table.width = "75%",grand_summary_row.background.color = "Navy",column_labels.background.color = "black",table.font.color = "black",row_group.border.bottom.color = "black",row_group.border.bottom.width = 2, row_group.padding = 10,row_group.background.color = "#EFFBFC",stub.font.weight = "bold") %>% tab_options(column_labels.hidden = F) %>% tab_style(style = list(cell_fill(color = "Grey")),locations = cells_body(rows = yearID == "yearID"))

[mbsteel]

Thanks, will give it a try!

Sent from my Sprint Samsung Galaxy S10e. Get Outlook for Androidhttps://aka.ms/AAb9ysg

---

From: Sumair Shah @.> Sent: Thursday, July 15, 2021 6:43:44 PM To: rstudio/gt @.> Cc: Meghan @.>; Mention @.> Subject: Re: [rstudio/gt] Repeating column labels for groups (a la "Freeze Panes" on Excel) (#194)

Hi @mbsteelhttps://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fmbsteel&data=04%7C01%7C%7C6ed203132433476be84208d947e200e2%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637619858266619679%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=kzY9o1AOggyf69%2BongGaEsarsQ6j%2BL6KOBL8lhAxkIw%3D&reserved=0 @stevenbedrickhttps://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fstevenbedrick&data=04%7C01%7C%7C6ed203132433476be84208d947e200e2%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637619858266629633%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=SJEjIGssoX8Gc2Nz4lYqTu7vIMohzdCRkWQ7eeh4l8Y%3D&reserved=0 @gitmoneyMSBAhttps://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2FgitmoneyMSBA&data=04%7C01%7C%7C6ed203132433476be84208d947e200e2%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637619858266629633%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=J1akS7x%2BgO2LzU2c63jN8BWEVSrdeTvbepfOHupYUf8%3D&reserved=0 -- sorry for the late response. Paste this into R and let me know if it makes sense. Super hacky way of doing this and after seeing the logic you may find a cleaner way. Let me know if you have any questions or run into trouble. You should get this result below [Rplot]https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fuser-images.githubusercontent.com%2F71948524%2F125866971-58fe00c8-3986-4d9f-9019-4fdc01ad07a0.png&data=04%7C01%7C%7C6ed203132433476be84208d947e200e2%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637619858266629633%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=eONzcZHahks5M9eY4lvxUX0NKtrGn0HuJL95XmbqsAQ%3D&reserved=0

library(janitor) library(gt) library(dplyr)

Just used this as a built-in R data set

B <- Lahman::Batting %>% tail(5)

Take the colnames

C <- colnames(B)

you can probably figure this part out better but for now, rbind colnames * length(df)

D <- as.data.frame(rbind(C,C,C,C,C),row.names = F,make.names = F) %>% janitor::row_to_names(1,remove_row = F)

Replace our column df with a vector of real data from the original df

D$playerID <- B$playerID

combine colnames and real data

final_df <- rbind(D,B)

you can hide or not hide column names.

final_df %>% arrange(desc(yearID)) %>% gt(groupname_col ="playerID")%>% tab_header(title = md("Advisees"),) %>% tab_options(heading.background.color = "#EFFBFC",stub.border.style = "dashed",stub.border.color = "#989898",stub.border.width = "1px",summary_row.border.color = "#989898",table.width = "75%",grand_summary_row.background.color = "Navy",column_labels.background.color = "black",table.font.color = "black",row_group.border.bottom.color = "black",row_group.border.bottom.width = 2, row_group.padding = 10,row_group.background.color = "#EFFBFC",stub.font.weight = "bold") %>% tab_options(column_labels.hidden = F) %>% tab_style(style = list(cell_fill(color = "Grey")),locations = cells_body(rows = yearID == "yearID"))

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHubhttps://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Frstudio%2Fgt%2Fissues%2F194%23issuecomment-881056646&data=04%7C01%7C%7C6ed203132433476be84208d947e200e2%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637619858266639591%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=l27QfV2Nbido8cOncQtpcsrs%2Bd4Ady1hIasT4%2FLrObA%3D&reserved=0, or unsubscribehttps://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fnotifications%2Funsubscribe-auth%2FAROYRNNAYJSB6S6UOUXA4ULTX5QCBANCNFSM4GZYVLFQ&data=04%7C01%7C%7C6ed203132433476be84208d947e200e2%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637619858266639591%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=l%2BLQkHgcExwjg42CpFZoDlnV88U1gGRd2tOdOPaSVx0%3D&reserved=0.

[stevenbedrick]

Thanks, @sumairshah2 ! I had been wondering if your trick was something along these lines (adding placeholder/dummy rows to serve as the headers), and had been starting to think about how I might do something similar in my situation but your way of doing it is much cleverer than what I had been coming up with. :-) Thanks so much for sharing!

[markbneal]

Hi, I've just made some mods to @sumairshah2 code to make some of the steps a bit clearer, might be useful to others? Apologies for the base R in places!

It seems quite possible that if you apply number formatting to the table, there might be a problem, because all columns in the final data frame end up as character.

#gt repeat headings by group
#https://github.com/rstudio/gt/issues/194#issuecomment-881056646

library(janitor)
library(gt)
library(dplyr)

# Just used this as a built-in R data set
my_data <- Lahman::Batting %>% tail(5)

# TO DO: make groupname as variable, then refer to throughout with {{}}?
# group_name

# 
groups <- my_data %>% 
  select(playerID) %>% 
  distinct()

# Take a count of groups
group_number <- my_data %>% 
  select(playerID) %>% 
  n_distinct()

## Make dummy data frame of headers, option 1
# Take the colnames
my_colnames <- colnames(my_data)

dummy_header_data <- data.frame(t(my_colnames)) %>% 
  janitor::row_to_names(1,remove_row = F)

#make a row for each group_number 
dummy_header_data[1:group_number,] <- dummy_header_data[1,]


## Make dummy data frame of headers, option 2

my_data_empty <- my_data[FALSE,]

my_data_empty[] <- lapply(my_data_empty, as.character)

dummy_header_data[1:group_number,] <- data.frame(t(colnames(my_data_empty)))

#purrr::map_dfr(seq_len(group_number), ~my_colnames) #more tidyverse-y, but not working


## Replace our column df with a vector of real data from the original df
dummy_header_data$playerID <- groups[,1]

# combine colnames and real data
final_df <- bind_rows(dummy_header_data, my_data %>% mutate_all(as.character))


## you can hide or not hide column names. 

#basic gt table
final_df %>% arrange(desc(yearID)) %>% 
  gt(groupname_col ="playerID")

#formatted gt table
final_df %>% arrange(desc(yearID)) %>% 
  gt(groupname_col ="playerID") %>% 
  tab_header(title = md("**Advisees**"),)  %>% 
  tab_options(
              heading.background.color = "#EFFBFC", 
              stub.border.style = "dashed", 
              stub.border.color = "#989898",
              stub.border.width = "1px", 
              summary_row.border.color = "#989898",
              table.width = "75%", 
              grand_summary_row.background.color = "Navy", 
              column_labels.background.color = "black",
              table.font.color = "black", 
              row_group.border.bottom.color = "black",
              row_group.border.bottom.width = 2, 
              row_group.padding = 10,
              row_group.background.color = "#EFFBFC",
              stub.font.weight = "bold"
              ) %>% 
  tab_options(column_labels.hidden = F) %>% 
  tab_style(style = list(cell_fill(color = "Grey")),
            locations = cells_body(rows = yearID == "yearID"))

[stevenbedrick]

And to join the fun, here's what I came up with for making the data frame of dummy rows! It's not quite as elegant as the example above; I used rep() on the vector of colnames and then matrix()'d it, rather than the clever multi-row-assignment syntax that @markbneal used:

# first figure out how many groups to add, via n_distinct() or however one wishes
num.groups <- my_data %>% select(grouping.column) %>% n_distinct()

# make a matrix of the column names, one per group
dummy.rows.mat <- matrix(rep(column.names, num.groups), nrow=num.groups, byrow=TRUE)

# turn into a data frame for rbinding with the original data 
dummy.rows.df <- as.data.frame(dummy.rows.mat, row.names=F, make.names=F) %>% janitor::row_to_names(1, remove_row = F)

For my particular use case, I only wanted to repeat certain column headers so I also have a step where I mutate() certain columns in dummy.rows.df into NAs, and then I use fmt_missing() to deal with those later in the table creation.

On an unrelated note, I would like to thank @rich-iannone and the rest of the gt devs for an amazing library. I'm working on a particularly gnarly table, and this is literally one of two things I've wanted to do with my table that I haven't been able to do using gt's built-in functionality. And thanks to the library's flexibility, this workaround ended up being conceptually pretty straightforward, which I see as being a real credit to the library and its core design.

[ArthurAndrews]

This would be a great feature. Similar to the scroll_box(fixed_thead = TRUE) option in kable_extra.

[mm225022]

I agree that having the ability to lock the header row would be extremely helpful. I have a large table right now in columns and rows; the fact that the column headers get lost when scrolling up definitely minimizes the usability of the table to the end users. I know that there are other options (as mentioned in this thread), but I would like to stay with the gt package.

[dmontecino]

This would be a great feature!

[RSA-Workspace]

The problem is related to overflow property set in the outer div and .cell-output-display. Just unset them and it works. Here is a working example:

mtcars %>% gt(id="two") %>% 
  opt_css(
    css = "
    .cell-output-display {
      overflow-x: unset !important;
    }
    div#two {
      overflow-x: unset !important;
      overflow-y: unset !important;
    }
    #two .gt_col_heading {
      position: sticky !important;
      top: 0 !important;
    }
    "
  )

All credits go to 1 and 2