Constructing Metadata Files

library(tidyverse)
library(bslib)
library(htmltools)
library(conflicted)

source("setup/conflicted.R")
source("setup/knit_engines_simple.R")
source("metadata/loris/loris_schedule.R")

Intro

The purpose of a metadata file is to organize the potential independent or predictor variables for your analysis into a single table with one row per SampleID. Then, when you produce a set of potential dependent or outcome values, you organize those into a similar structure with the SampleIDs organized rowwise to streamline the process of matching predictor variables to outcome variables by SampleID. It’s good practice to keep as much of your information in one tidy table as possible so that you can keep pulling from that source to further filter, wrangle and analyze without losing track of different versions and datasets over time. That means you should brainstorm as many possible predictor variables you might use in downstream analysis as possible and organize them into one tidy table where each SampleID is matched to a value for every variable. You will end up ignoring most of these variables as you construct individual tests and visuals later, so consider this simply a rough draft of your information of interest. I am going to do this with the Pygmy Loris dataset for this tutorial. You may have a very different set of variables to organize for your own project.

How to Organize Tidy Metadata

Your goal is to match every variable to some indexing column(s) that you can reliably match to each of your experimental “observations” or “samples.” In this example, I am working with data from our Loris Microbiome study, where we are going to consider each day of sample collection for each subject one “observation”. That means I want to construct a metadata table where every row represents one day and one subject, and every column contains measurements of other variables for that subject on that day. This makes it easy for us to then match any sample ID to one row of the metadata table and relate those variables to our microbiome profiles.

Other Cases

In other cases, you may find yourself organizing your metadata into broader units. For example, if we are not interested in a time dimension but are comparing variables across different locations, we may organize our metadata into one row per location. Or if we are comparing something across species, we would have one row per species.

Previous Scripts

If you want to match your samples to the metadata now, you should use the SampleInventory script to organize your sample IDs by date.

Still, you can really put your metadata together at any stage, as long as you know how you will need to match your sample inventory to these variables later, so it is not really necessary to complete any of the previous scripts before this.

External Files for Metadata

You may be importing metadata organized in many different ways. In this case, I am providing an example R script and tsv table to use in this case.

R Script

# metadata/loris/loris/loris_schedule.R

keeper_notes <- list(
  list(date = ymd("2024-01-22"), tag = "food" , subject = list("culi") , note = "removing cauliflower and broccoli from diet rotation"),
  list(date = ymd("2024-02-20"), tag = "food" , subject = list("culi") , note = "removing tomato from diet rotation"),
  list(date = ymd("2024-03-21"), tag = "repro" , subject = list("culi")             , note = "Culi whistling and actively attempting to get to Warble"),
  list(date = ymd("2024-04-04"), tag = "repro" , subject = list("culi", "warble"), note = "Observed several breeding events"),
  list(date = ymd("2023-11-16"), tag = "health", subject = list("culi")             , note = "had what appeared to be dried blood around his anus and tail"),
  list(date = ymd("2023-12-17"), tag = "health", subject = list("culi")             , note = "coprophagy observed"),
  list(date = ymd("2024-01-21"), tag = "health", subject = list("culi")             , note = "coprophagy observed"),
  list(date = ymd("2024-02-10"), tag = "health", subject = list("culi")             , note = "left biscuit and some gum"),
  list(date = ymd("2024-04-01"), tag = "repro" , subject = list("warble")           , note = "swollen vulva"),

Nutrition Table

# metadata/loris/nutrition.tsv

"diet_name" "class" "item"  "fed"   "units" "relative"  "relative_to"
"Baseline"  "Foods" "Biscuit rotation"  13200   "mg"    0.252390057361377   "diet_total"
"Baseline"  "Foods" "Invertebrate, misc"    10000   "mg"    0.191204588910134   "diet_total"
"Baseline"  "Foods" "Mazuri enrich gum arabic (5b35)"   12000   "mg"    0.229445506692161   "diet_total"
"Baseline"  "Foods" "Protein rotation"  2100    "mg"    0.0401529636711281  "diet_total"
"Baseline"  "Foods" "Seasonal vegetables"   15000   "mg"    0.286806883365201   "diet_total"
"Biscuit elimination"   "Foods" "Biscuit rotation"  0   "mg"    0   "diet_total"
"Biscuit elimination"   "Foods" "Invertebrate, misc"    18000   "mg"    0.257142857142857   "diet_total"
"Biscuit elimination"   "Foods" "Mazuri enrich gum arabic (5b35)"   17000   "mg"    0.242857142857143   "diet_total"
"Biscuit elimination"   "Foods" "Protein rotation"  5000    "mg"    0.0714285714285714  "diet_total"

Bristol Table

# metadata/loris/bristols.tsv

date    subject bristol_min bristol_max bristol_mean
10/26/23    culi    3   5   4
10/26/23    warble  1   1   1
10/27/23    culi    3   6   4.5
10/27/23    warble  1   1   1
10/28/23    culi    3   3   3
10/28/23    warble  1   1   1
10/29/23    culi    3   3   3
10/29/23    warble  1   1   1
10/30/23    culi    3   3   3

Script

Organizing Dates

The toughest variables to match and wrange in R are often dates, especially when you are dealing with both states (start and end dates for intervals) and events (single dates). For studies like this one based around daily sample collection, I find it easiest to start by populating a blank dataframe with one row per sample collection day. Then I match date-based variables to this dataframe and join my SampleIDs to it.

Once we combine all the pieces together, we will have a metadata table with one row per day for each subject. Then we can match every sample to subject and day using the same table.

collection_start  <- ymd("2023-10-26")
collection_end    <- ymd("2024-12-14")
collection_date  <- seq.Date(from = collection_start, 
                             to   = collection_end, 
                             by   = "day")
subject          <- c("warble", "culi")

subjects_dates <- expand_grid(subject, collection_date)

collection <- tibble(collection_date = collection_date) %>%
  mutate(study_day  = row_number(),
         day_week   = wday(collection_date, label = TRUE),
         month      = month(collection_date, label = TRUE),
         study_week = if_else(day_week == "Thu", (study_day + 6)/7, NA),
         study_month = consecutive_id(month)
         ) %>%
  fill(study_week) %>%
  select(
    collection_date,
    study_month,
    study_week,
    study_day
  ) %>%
  right_join(subjects_dates, by = "collection_date")

collection

Construct Variables

Health

Diet

We have only two subjects in this dataset: Culi and Warble. Culi underwent a series of diet trials that we organized into start and end dates, while Warble maintained a baseline diet throughout our study.

diet_trials[[1]]$begin <- floor_date(collection_start, "month")

nutrition_wide <- nutrition %>%
  mutate(diet = fct_recode(as.factor(diet_name), !!!diet_factors)) %>%
  relocate(diet) %>%
  select(-diet_name) %>%
  filter(diet != "Other")
nutrition_wide

ends   <- diet_trials %>% discard_at(1) %>%
  map(\(x) list_assign(x, end = x$begin - day(1))) %>%
  map(\(x) keep_at(x, "end")) %>%
  list_flatten() %>%
  list(., list(end = (ceiling_date(collection_end, "month") - days(1)))) %>%
  list_flatten()
diets <- map2(
  diet_trials, 
  ends, 
  \(x, y) list_assign(x, end = y, collection_date = seq(x$begin, y))) %>%
  enframe(name = NULL)  %>%
  unnest_wider(value) %>%
  select(diet, collection_date) %>%
  unnest_longer(collection_date) %>%
  mutate(subject = "culi") %>%
  bind_rows(diets_warble)
diets

Supplements

ends   <- supplements %>% discard_at(1) %>%
  map(\(x) list(x$begin - day(1))) %>%
  list_flatten()

meds <-  supplements %>% discard_at(21) %>%
  map2(
    ends, 
    \(x, y) list_assign(x, end = y, collection_date = seq(x$begin, y))) %>%
  map_depth(1, \(x) keep(x, \(y) all(y > 0))) %>%
  discard(\(x) all(length(x) < 4)) %>%
  enframe(name= NULL) %>%
  unnest_wider(value) %>%
  pivot_longer(
    c("probiotic", "steroid", "fiber", "antibiotic", "antidiarrheal"),
    names_to       = "supplement",
    values_to      = "dose",
    values_drop_na = T
    ) %>%
  select(collection_date, supplement, dose) %>%
  arrange(supplement, dose) %>%
  left_join(supplement_details, by = "supplement") %>%
  rowwise() %>%
  mutate(dose_rel = dose/dose_high) %>%
  ungroup() %>%
  unnest_longer(collection_date)
meds

meds_wide <- meds %>%
  select(collection_date, supplement, dose_rel) %>%
  pivot_wider(
    names_from  = "supplement", 
    values_from = "dose_rel", 
    values_fill = 0) %>%
  mutate(subject = "culi") %>%
  bind_rows(meds_warble)
meds_wide

Diet + Meds

nutrition_meds <- collection %>%
  select(collection_date, subject) %>%
  left_join(diets, by = join_by(collection_date, subject)) %>%
  left_join(meds_wide, by = join_by(collection_date, subject)) %>%
  mutate(across(where(is.numeric), ~replace_na(., 0))) %>%
  left_join(nutrition_wide, by = "diet")
nutrition_meds

Bristol Scores

bristols_warble <- collection %>%
  filter(subject == "warble") %>%
  mutate(bristol = 1)

bristols <- read_csv(
  "metadata/loris/originals/hdz_nutritionData_2025-1-30.csv",
  col_types = cols_only(
    Date = col_date(format = "%m/%d/%y"),
    "Fecal Score for Culi" = col_character()
  )
) %>%
  rename(bristol = "Fecal Score for Culi", collection_date = Date) %>%
  separate_longer_delim(bristol, ",") %>%
  mutate(bristol = as.integer(as.character(str_remove_all(bristol, "[^\\d]")))) %>%
  filter(!is.na(bristol) & bristol != 0) %>%
  group_by(collection_date) %>%
  reframe(bristol = mean(bristol)) %>%
  ungroup() %>%
  right_join(filter(collection, subject == "culi"), by = join_by(collection_date)) %>%
  arrange(collection_date) %>%
  mutate(mean_weekly  = mean(bristol, na.rm = TRUE), .by = "study_week") %>%
  rowwise() %>%
  mutate(bristol = replace_na(bristol, mean_weekly)) %>%
  ungroup() %>%
  bind_rows(bristols_warble) %>%
  select(collection_date, subject, bristol) %>%
  arrange(collection_date)

Social/Repro

dates_pair <- select(collection, collection_date, subject) %>%
  left_join(warble_cycles, by = join_by(collection_date == date)) %>%
  left_join(access_dates, by = join_by(collection_date == date)) %>%
  mutate(warb_cycle   = replace_na(warb_status, "anestrus"), 
         pair_access  = replace_na(pair_access, "n"), 
         .keep = "unused") %>%
  left_join(keeper_notes, by = join_by(collection_date == date, subject)) %>%
  left_join(holdings, by = join_by(collection_date, subject)) %>%
  select(collection_date, subject, warb_cycle, pair_access, holding, keeper_note)
dates_pair

Complete Metadata Table

metadata <- collection %>%
  left_join(nutrition_meds, by = join_by(collection_date, subject)) %>%
  left_join(bristols      , by = join_by(collection_date, subject)) %>%
  left_join(dates_pair    , by = join_by(collection_date, subject)) %>%
  relocate(
    bristol,
    holding, 
    warb_cycle, 
    keeper_note,
    .after = steroid
  )

save(metadata, file = "metadata/loris/metadata.RData")

write_csv(metadata, "metadata/loris/metadata.csv")

metadata

Timeline Option for Plotting

I’ve also found that it is helpful to store a tibble with rows organized by date intervals for events or states that might be plotted on a timeline or timeseries graph for context. I will bring in the same data with different shapes and export this as a tibble for later.

source("metadata/loris/loris_timeline.R")

cat(
  read_lines("metadata/loris/loris_timeline.R", n_max = 20),
  sep = "\n"
)

keeper_notes <- list(
  list(date = ymd("2024-01-22"), tag = "food" , subject = list("culi") , note = "removing cauliflower and broccoli from diet rotation"),
  list(date = ymd("2024-02-20"), tag = "food" , subject = list("culi") , note = "removing tomato from diet rotation"),
  list(date = ymd("2024-03-21"), tag = "repro" , subject = list("culi")             , note = "Culi whistling and actively attempting to get to Warble"),
  list(date = ymd("2024-04-04"), tag = "repro" , subject = list("culi", "warble"), note = "Observed several breeding events"),
  list(date = ymd("2023-11-16"), tag = "health", subject = list("culi")             , note = "had what appeared to be dried blood around his anus and tail"),
  list(date = ymd("2023-12-17"), tag = "health", subject = list("culi")             , note = "coprophagy observed"),
  list(date = ymd("2024-01-21"), tag = "health", subject = list("culi")             , note = "coprophagy observed"),
  list(date = ymd("2024-02-10"), tag = "health", subject = list("culi")             , note = "left biscuit and some gum"),
  list(date = ymd("2024-04-01"), tag = "repro" , subject = list("warble")           , note = "swollen vulva"),
  list(date = ymd("2024-04-02"), tag = "repro" , subject = list("culi")             , note = "swollen testicles"),
  list(date = ymd("2024-04-03"), tag = "repro" , subject = list("culi", "warble"), note = "Keeper Zach entered the facility in the morning to clean and found Warble and Culi sleeping together in a hammock in room 3"),
  list(date = ymd("2024-04-03"), tag = "repro" , subject = list("culi", "warble"), note = "Observed breeding event"),
  list(date = ymd("2024-04-04"), tag = "repro" , subject = list("warble")           , note = "Observed whistling"),
  list(date = ymd("2024-04-04"), tag = "repro" , subject = list("culi", "warble"), note = "JM and AMR found Warble and Culi sleeping together in a hammock in room 3"),
  list(date = ymd("2024-04-04"), tag = "repro" , subject = list("warble")         , note = "swollen vulva"),
  list(date = ymd("2024-04-06"), tag = "repro" , subject = list("warble")         , note = "pregnancy suspected"),
  list(date = ymd("2024-04-14"), tag = "health", subject = list("culi")             , note = "weight = 451 g"),
  list(date = ymd("2024-05-13"), tag = "health", subject = list("warble")           , note = "weight = 467 g"),
  list(date = ymd("2024-06-17"), tag = "health", subject = list("culi")             , note = "left pieces of food in room 5"),

meta_timeline <- diet_trials %>%
  bind_rows(access_pair) %>%
  bind_rows(holdings) %>%
  bind_rows(repro_dates) %>%
  bind_rows(supplements) %>%
  bind_rows(keeper_notes) %>%
  arrange(start, end)

save(meta_timeline, file = "metadata/loris/meta_timeline.RData")
write_csv(meta_timeline, "metadata/loris/meta_timeline.csv")

meta_timeline

Compile with Samples (Optional)

If you would like to bind this to sample data to create the sample table now…

load("../read_processing/samples/loris/inventories/compilation_seq_records.RData")

compilation_seq_records <- compilation_seq_records %>%
  mutate(subj_conf = if_else(subject %in% c("culi", "warble"), "yes", "no")) %>%
  mutate(subject = str_remove_all(subject, "[^\\w+]"))

compilation_metadata <- metadata %>%
  nest(
    health = c(
      bristol,
      warb_cycle,
      keeper_note
    ),
    treatments = c(
      diet,
      antibiotic,
      antidiarrheal,
      fiber,
      probiotic,
      steroid
    ),
    nutrition = c(
      Total_total:Vitamins_lycopene
    ),
    environment = c(
      holding,
      pair_access
    )
  ) %>%
  right_join(compilation_seq_records, 
             by = join_by(subject, collection_date)) %>%
  select(
    study_month,
    study_week,
    collection_date,
    subject,
    study_day,
    sampleID,
    extracted,
    sequenced,
    extracts,
    libraries,
    filtered_samples,
    health,
    treatments,
    nutrition,
    environment
  ) %>%
  arrange(collection_date) %>%
  group_by(collection_date) %>%
  fill(study_month, study_week, study_day) %>%
  ungroup() %>%
  mutate(study_day  = if_else(
    is.na(study_day) & !is.na(collection_date), 
    lag(study_day) + 1, 
    study_day)) %>%
  mutate(study_week = if_else(
    is.na(study_week) & lag(study_week) == lead(study_week), 
    lag(study_week), 
    study_week
    )
    ) %>%
  group_by(study_week) %>%
  fill(study_month) %>%
  ungroup()

save(compilation_metadata, file = "metadata/loris/compilation_metadata.RData")

loris_sample_table <- compilation_metadata %>%
  filter(sequenced > 0) %>%
  select(study_month:sampleID, filtered_samples:environment) %>%
  mutate(obs_id = as.character(str_glue(
    "{str_sub(subject, 1L, 4L)}{study_day}"
    ))) %>%
  unnest(filtered_samples) %>%
  select(
    obs_id,
    alias,
    subject,
    study_month,
    study_week,
    collection_date,
    study_day,
    seqrun,
    read_count,
    treatments,
    health,
    environment,
    nutrition
  ) %>%
  unnest(treatments, health, environment, nutrition) %>%
  distinct()

write_csv(loris_sample_table, "data/loris/microbiome_sample_table.csv")

sample_table <- loris_sample_table %>%
  column_to_rownames("alias")

save(sample_table, file = "data/loris/sample_table.RData")
sample_table

Next Steps

Now you should proceed to a Bioinformatics or Statistical Workflow to Begin Merging Metadata with Results for Analysis.