An example of a data index for navigation through metadata and preview images

Author

G.Fraga Gonzalez

Published

December 11, 2024

This is a demo of a data index that allows to navigate through a table with metadata and thumbnail images. The idea is to facilitate finding and sharing your data. Visit our Tutorials page for more details on the workflow of producing such a data index. In short, for the index you need: (1) a source table of structured metadata (2) if you are to display thumbnails you need the picture files (3) some R code to render the table interactive with the package DT and (4) R Quarto to output the table as an HTML page.

This demo uses a dummy dataset, i.e., the source metadata table and images have no meaning. The example demonstrates three frequent operations that can be useful for creating a data index:

  • Reading filenames and extracting information into columns from filename parts

  • Joining two metadata tables according to a key variable with shared values between them

  • Adding some HTML code to a column value (in this case filenames) to render the images (the same approach would be done to render hyperlinks).

Instructions

Use the filter boxes to select which data are displayed. Use select Columns to choose which columns are displayed or change the order of the column if you click and drag on the variable names. You can select rows (one or multiple) clicking on them or by using the select button. Click on a thumbnail to open the full-size image. Click the button Copy to copy the filtered or selected rows to the clipboard and click the button csv to save it in that format.

Code
library(crosstalk) # for filter boxes
library(DT) # For html table

# first we need an object that will be shared by filter panels and datatable 
shared_joined <- SharedData$new(tbl_joined, key = ~subjID, group = "shared_obj")

# to make two columns: one with filter panels and one with the table
bscols(widths = c(2,10),
     device = c("xs", "sm", "md", "lg"),

# filter panels. Other formats are sliders and checkboxes https://rstudio.github.io/crosstalk/using.html
list(
 filter_select( id = "subjID", label = "subject",sharedData = shared_joined, group = ~subjID),
 filter_checkbox("Sex","Sex",shared_joined, ~Sex, inline = FALSE)
),

# table
datatable(
    shared_joined,
    #filter = "top",
    escape = FALSE,
    rownames = FALSE,
    width = "100%",
    class = 'cell-border  hover', 
    extensions = c('Buttons', 'Select','ColReorder', 'Scroller', 'FixedHeader', 'KeyTable'),
    selection = 'none',
    options = list( 
      fixedHeader = TRUE,
      pageLength = 20,
      paging = FALSE,
      dom = 'Bftrip',
      #buttons = c('colvis','selectAll', 'selectNone', 'copy', 'csv'),  
      buttons = list(list(extend = "colvis", text = "select Columns", background='yellow'),
                     'selectAll', 'selectNone', 'copy', 'csv'), 
      select = list(style = 'os', items = 'row'),
      scrollX = TRUE,
      scrollY = "800px",
      scrollCollapse = TRUE,  
      autoWidth = TRUE,
      colReorder = TRUE,
      columnDefs = list(
        list(
          keys = TRUE,
          search = list(regex = TRUE),
          targets=0
        )
      )
     )
  )
)

The following steps will vary depending on the index content. Steps 1 to 4 will change depending on the used metadata tables and if we want to render links, images, etc. Step 5 will vary depending on the many possible options we have to display the table and filters. For this example we did:

1. Read metadata table

We first read a source metadata table with information about subjects and experiments (image recording).

Code
library(dplyr)
tbl <-  readxl::read_excel('DummyData1_20241234_subjects.xlsx',na=c("","N/A"))

# Minor adjustment of time format
colsWithTime <- colnames(tbl)[grep('*time*',colnames(tbl))] # find variables with 
tbl <- tbl %>%  mutate(across(all_of(colsWithTime), ~ format(., format = "%H:%M")))

2. Make a table with filenames

We read the names of files with the images we want to showcase. The files with subject images start with subject identifier separated by the rest by a hyphen ‘-’. We use this to create a new variable containing the subject identifier of each image file.

Code
files <- dir('Images') # Find all files in our images folder
fname <- files[grepl(paste0('*.jpg$'),files)] #take only .jpg files

tbl_files <- as.data.frame(fname) # make table with filenames
tbl_files$subject <- sapply(strsplit(fname,'_'),'[[',1) #take 1st filename part

3. Join the two tables

Now we link the image filenames to the source metadata table.

Code
tbl_joined  <- full_join(x=tbl,
                         y = tbl_files,
                         by=join_by("subjID"=="subject"),
                         keep=FALSE)

4. Add HTML code to render the images

Since we want to display images we will add some HTML including the address of the image files (the repository). The URL is added as href so that we access the image when we click. The path relative to this script is added to src so that the table renders the image thumbnail. We use file.path() to write the directories to avoid having to specify the operating system separator ‘\’ or ‘/’ (except for the URL address).

Code
# define image paths
pic_folder <- 'https://gitlab.uzh.ch/crsuzh/afford_website/-/tree/master/ORD_index/Images'
pic_fullpath <- file.path(pic_folder,tbl_joined$fname)
pic_relpath <- file.path('..','Images',tbl_joined$fname)

# add paths and HTML code 
tbl_joined$pic <- paste0('<a href=\'', pic_fullpath,'\' target=\'_blank\'>', 
                       '<img src=\'',pic_relpath, '\' height=\'70\'></a>')

# Add location 
tbl_joined$location <- paste0('<a href=\'',pic_folder,'\' target=\'_blank\'>', 
                       'Gitlab folder >>','</a>')


# Move to first column
tbl_joined <- tbl_joined %>% relocate("pic",.before = 1)
tbl_joined <- tbl_joined %>% relocate("fname",.before = 2)
tbl_joined <- tbl_joined %>% relocate("location",.before = 3)

The img src usually has the path to the images relative to the current path, e.g., “.” if they are in subfolder of the current working directory (in R you can find it with getwd()). The current path “../../Images” indicates they are in a folder named ‘Images’ two folders above the current folder. This is related to how the Continuous integration (CI/CD) pipeline is set. There are many other possible configurations. Attention: your local relative paths may not function the same if you use CI/CD and Gitlab pages. Find more information about CI/CD is in the Tutorial section.

5. Render the HTML table with DT package

Finally we render the table interactive and add some custom filter boxes. We need two packages (they were installed in advanced with install.packages() and then we load their libraries with the command library()). The main package is DT which allows to render the table interactive. The R package crosstalk is used to allow filter boxes to change what rows we see in the table. Its function bscols() allows to lay out the filters on a column next to the table.

Code
library(crosstalk) # for filter boxes
library(DT) # For html table

# first we need an object that will be shared by filter panels and datatable 
shared_joined <- SharedData$new(tbl_joined, key = ~subjID, group = "shared_obj")

# to make two columns: one with filter panels and one with the table
bscols(widths = c(2,10),
     device = c("xs", "sm", "md", "lg"),

# filter panels. Other formats are sliders and checkboxes https://rstudio.github.io/crosstalk/using.html
list(
 filter_select( id = "subjID", label = "subject",sharedData = shared_joined, group = ~subjID),
 filter_checkbox("Sex","Sex",shared_joined, ~Sex, inline = FALSE)
),

# table
datatable(
    shared_joined,
    #filter = "top",
    escape = FALSE,
    rownames = FALSE,
    width = "100%",
    class = 'cell-border  hover', 
    extensions = c('Buttons', 'Select','ColReorder', 'Scroller', 'FixedHeader', 'KeyTable'),
    selection = 'none',
    options = list( 
      fixedHeader = TRUE,
      pageLength = 20,
      paging = FALSE,
      dom = 'Bftrip',
      #buttons = c('colvis','selectAll', 'selectNone', 'copy', 'csv'),  
      buttons = list(list(extend = "colvis", text = "select Columns", background='yellow'),
                     'selectAll', 'selectNone', 'copy', 'csv'), 
      select = list(style = 'os', items = 'row'),
      scrollX = TRUE,
      scrollY = "800px",
      scrollCollapse = TRUE,  
      autoWidth = TRUE,
      colReorder = TRUE,
      columnDefs = list(
        list(
          keys = TRUE,
          search = list(regex = TRUE),
          targets=0
        )
      )
     )
  )
)
Back to top