Formalize joint index support in .tsv via {"+".join(entities)}s.tsv files

[yarikoptic]

Your idea

Came up prominently in discussion of the BEP036 phenotypic data (attn @bids-standard/bep036):

• [ENH] BEP036 - Phenotypic Data Guidelines #2123 (comment)

which wants to introduce an aggregated demographics table, bringing together information across subjects/sessions and potentially even runs.

In immediate use case of BEP036 I suggest to formalize allowing for top level participant+sessions.tsv with joint index across participant_id, session_id pairs. Then information

• the same across sessions will go to participants.tsv like it does now
• the same across participants but different across sessions - into sessions.tsv at the top level to be "taken" per IP principle.
  • @effigies noted complication is that for .tsv we actually do not really define how to inherit .tsv records beyond "take .tsv closest in hierarchy"... I am yet to check.

But I think the issue is common and warrants a more generic solution! Related desires came up in

• iEEG in BIDS and microephys (BEP032) to need to disambiguate combination of electrodes on probes. iEEG atm just encodes some location in addition to index within "name", thus making it unique

It is IMHO potentially a general desire to be able to consolidate relevant summary information in the "long form" at higher levels, thus requiring composite indexes. E.g. for an "experimental" composition of OpenNeuro studies I already produced similar studies_derivatives.tsv (to be renamed likely into study+derivatives.tsv).

Pros:

• It would "pair" up with summarization inheritance principle that at higher levels, if desired, we can provide common metadata in .json or .tsv files. So ATM participants.tsv provide common metadata per participant and sub-*/sub-*_sessions.tsv are the ones to provide differing ones across sessions.
• It would allow for new type of metadata aggregation/specification, not anyhow limited/specific to phenotype
  • would not break any existing tool assuming a single index/unique IDs in {plural_entity}.tsv} and thus be backward compatible
  • allow for efficient single-entity index table to still exist (unlike adding composite index into a {plural_entity}.tsv needing to duplicate the value for all values of the other entities)

Counter precedents (sneaked in):

• microscopy uses top level samples.tsv which at the top level pairs the two: "The combination of sample_id and participant_id MUST be unique."
  • TODO: rename/auto-migrate into sample+participants.tsv while allowing for samples.tsv as well
• any other???

Alternative solutions

Flexible joint indexes

By @effigies to do allow multiple columns to serve an index, and define that at the schema level. Details to be provided by @effigies in some other issue/PR? ;)

Related

• Uniformize .json files for .tsv's first column to be "<entity>_id" bids-2-devel#69
• Formalize "life-cycle"/movement and placement of metadata bids-2-devel#85

[ericearl]

@yarikoptic A few contrary thoughts:

1.

I suggest to formalize allowing for top level participant+sessions.tsv with joint index across participant_id, session_id pairs.

The schema already supports multiple index columns, some required and others optional. See this code block, for instance:

https://github.com/bids-standard/bids-specification/pull/2123/files#diff-6ed70b0e6833853c1cbb280f53bc109d484c18f019c147d8ffc050bcbb81cfe7R33-R66

2.

I think the issue is common and warrants a more generic solution

I feel the current implementation of the index columns for validating against the schema is satisfactory. Rather than relying on error-prone file-naming for controlling the index columns, the schema is more certain and protected by continuous integration checks in pull requests, etc.

3.

It is IMHO potentially a general desire to be able to consolidate relevant summary information in the "long form" at higher levels, thus requiring composite indexes.

You listed the "Pros" just below this quote, but not the "Cons". I'll try to mention a few.

Cons:

• The composite indexes idea seems to require two different names for the same thing and to define the difference between singular and plural. For example, with your studies_derivatives.tsv (to be renamed likely into study+derivatives.tsv). Let's assume you go with the name "study+derivatives.tsv". Immediately BIDS needs three rules that are non-trivial to implement.
  1. Each index column in the file name MUST have a corresponding column in the TSV header whose suffix is "_id".
  2. The file names study+derivatives.tsv and derivative+studys.tsv must be equal and equivalent as the intention is that those two are joint indexes.
  3. If someone comes along with a study+derivative+versions.tsv as a composite index, it makes me wonder the utility of the file name as an indicator of the index columns in the first place.
• I don't know of any "existing tool assuming a single index/unique IDs" inside a participants.tsv file. Optimizing for a non-existent or edge use case is not a good use of effort.
• Everyone collects tabular phenotypic data, but not much is shared due to the burden of preparing it in a standardized format. Psych-DS has this figured out and allows minimal metadata to be used. BIDS expects more metadata, but I feel BIDS should not be imposing more strict rules on the curators, as much as those developing schema.

In summary

I fear you are trying to optimize for a non-80% problem, which is not BIDS' territory. The BEP036 leads and other contributors have been at these guidelines for a long time now and have a minimal set ready to go that will benefit curators of tabular phenotypic data. I don't want to hold up BEP036 any longer for use cases that will have <20% impact.

[yarikoptic]

Thank you @ericearl for the feedback, pointers and putting your thought to it! I will try to be concise so would not quote.

1. Indeed we already have the composite index feature support (in schema) which is quite non-generic as used only in 2 cases:

❯ git grep -p index_columns.*, -- src/schema
src/schema/rules/tabular_data/emg.yaml=EMGElectrodes:
src/schema/rules/tabular_data/emg.yaml:  index_columns: [name__electrodes, group__emg]
src/schema/rules/tabular_data/modality_agnostic.yaml=Samples:
src/schema/rules/tabular_data/modality_agnostic.yaml:  index_columns: [sample_id, participant_id]

and thus, as such, IMHO it could be called ad-hoc and warrant better formalization since now coming by {entityplural}.tsv in >80% of cases you would expect a single index. So, ATM we have some hidden in the schema explit rule on how many index columns to expect and for user it is not really explicit. Imagine that we reach 50/50 ratio between single vs plural indexes across .tsv's -- it would become cognitive load! My point here is not about implementation which, as you pointed out, already exists, but rather about

• making it "explicit" for humans on what to expect in terms of indexing for a particular .tsv file.
• allow for having files per-entity with per-entity index while also allowing for composite index where needed. E.g. in existing examples above to have dedicated samples.tsv to sample labels like pre and post (e.g. "description") while them participants.tsv to describe those subjects, and samples+participants.tsv describe properties unique to combinations (e.g. weight). Currently it is not possible without duplicating of the same value ("description") across the rows

3. great examples! they are hardly cons to me: with clear formalization we actually remove the burden of custom rules to implement -- we implement a single logic for formalization, which would then apply to all file names which would be composed that way from entities:

• indeed we would have {entity}_id per each entity -- it is ok, you can see that already in above for samples.tsv. (The suffix one for EMGElectrodes is for the _electrodes.tsv which is a different beast, see 2nd level of expansions in description of Formalize the concept of [{leading entities}_]{entity_plural}.{tsv,json} file(s) #2283 for overview there)
• order of the entities is maintained following the order we have already formalized in https://github.com/bids-standard/bids-specification/blob/master/src/schema/rules/entities.yaml -- so there (ATM) could only be one way to order them
• nobody can "come up" with study+derivative+versions.tsv until we have all those entities defined. My example for studies and derivatives is suboptimal here as looks far into the future of BIDS 3.0 or 4.0 ;) but if we adopt this formalization, that future might come sooner since we would have all machinery ready and would just need to add new entities while not needing to change anything else potentially.
• re tools -- whatever shell oneliner I ever composed would have relied on just getting a single line... and I would expect any tool which cares about loading that metadata expecting a single line not a list of lines to get through. Didn't check in detail but

here are results from claude for my query

 In BIDS, in participants.tsv we expect only one row per each participant_id (first column). Search internet and github for uses of participants.tsv and see if any tool relies on that fact of having just one row - as
returns a singular entry instead of a list of some dictionary of entries given a singular participant_id or a list of participant_ids

● Based on my research, I can now provide a summary of the findings:

Summary: Tools Relying on Unique participant_id in participants.tsv

Confirmed: MNE-BIDS Assumes Uniqueness

MNE-BIDS is the clearest example of a tool that relies on unique participant_id values. In mne_bids/read.py, the _handle_participants_reading() function uses Python's .index() method to look up subjects:

subjects = participants_tsv['participant_id']
row_ind = _verbose_list_index(subjects, subject, allow_all=True)

This returns only the first match - if duplicate participant_id values exist, subsequent rows are silently ignored. The function then extracts a single row's data to populate raw.info["subject_info"] with fields like his_id, birthday, sex, hand, weight, and height.

PyBIDS Behavior

PyBIDS doesn't have a direct single-subject dictionary lookup method for participants.tsv. When working with participant-level variables:

• It loads the entire TSV as a DataFrame
• It renames participant_id to subject and strips the sub- prefix
• There's an Merge metadata from outside the JSON sidecar tree (e.g. participants.tsv)? pybids#78 requesting that get_metadata() merge participants.tsv data

NiBabies/NiPreps

NiBabies parses participants.tsv to extract age for selecting surface reconstruction methods. While the exact lookup code isn't explicit in the files I examined, the functionality is designed for single-subject processing pipelines where you'd expect one row per participant.

BIDS Validator

The validator checks for:

• Duplicate column headers (enforced since PR Revisiting space definition in BIDS (reference frames, coordsys) with respect to Motion extension #1488)
• Proper participant_id format (sub-<subject_id> pattern)

However, it does not appear to check for duplicate participant_id rows - only column name uniqueness.

Potential Impact

Tools using .index() or similar single-result lookups would:

1. Return only the first matching row
2. Silently ignore any duplicate entries
3. Potentially cause data inconsistency without warning

Sources:

• https://github.com/mne-tools/mne-bids/blob/main/mne_bids/read.py
• Merge metadata from outside the JSON sidecar tree (e.g. participants.tsv)? pybids#78
• https://github.com/bids-standard/bids-validator/issues/1530
• https://mne.tools/mne-bids/stable/auto_examples/read_bids_datasets.html
• https://github.com/nipreps/nibabies/blob/master/CHANGES.md
• Improve spec/validation of participants.tsv #458

• As long as things are obvious and explicitly documented, I do not see here any added burden... actually I see lesser burden since would remove question about how many index columns in any particular .tsv file.

[ericearl]

Thanks for keeping the conversation going @yarikoptic. I would like to find a low or no cost solution for BIDS to adopt these required and optional index columns too!

1. Multiple index columns examples

The schema solution already works. It would be a lot of extra effort, as estimated by @effigies and @rwblair, to implement a new technique for demarcating index columns. Take a closer look at the three extra instances of it in the BEP036 schema.

1. Participants
2. Phenotypes
3. Sessions

They render the tables correctly already, which is what I believe you are wanting for index columns documentation clarity. Look at their three instances in the BEP036 preview spec.

1. Participants file table
   
2. Phenotypic and assessment data table
   
3. Sessions file table
   

2. New multiple index columns proposed requirements

[At the moment] we have some hidden in the schema explicit rule on how many index columns to expect and for user it is not really explicit.

I hope my evidence provided just above here in point "1." refutes this for you.

making it "explicit" for humans on what to expect in terms of indexing for a particular .tsv file.

I would rather explicitly define the index columns in the JSON header than to use the name of the file to determine index columns. If that would satisfy your requirement, I will happily re-write that into BEP036 as a RECOMMENDED JSON sidecar field, IndexColumns.

I think when people are referencing the participants.tsv, they should expect all the information about participants in that file whether it spreads across sessions or not. Likewise, I think all the information about sessions should be in the sessions.tsv file.

I also don't want to limit people to storing common participant demographics in just one of the three sensible places (participants file, sessions, file, or a tabular phenotype file). At the end of the day, users are likely to join the tabular data on shared column indexes to get their participant table for analysis.

Currently it is not possible without duplicating of the same value ("description") across the rows

I am fine with dataset curators commonly duplicating the same value across rows in a longitudinal or multi-sample study. That extra column doesn't really hurt the file size and saves the more technical need to join smaller tables like I think you're suggesting.

BEP036 is targeting less technical users who just have tabular data they want to standardize. I don't want dataset curators to have to make a database schema before they can output tabular files in a standardized format. The most valuable place they can spend time is on their tabular phenotypic data dictionaries. Messy tables are welcome!

3. The +-joining problem

Instead of assuming study+derivative+versions.tsv, let's assume the more real participant+session+runs.tsv which is what I believe you are proposing for multi-session multi-run phenotypic data and sessions files. Why stop at 2 or 3 index columns? I think rather than formalizing the file naming, the "intent" of the files should be clearest in the bids-specification. I don't want to see a file tree where there's:

ds123456789/

• phenotype/
• participants.json
• participants.tsv
• participant+sessions.json
• participant+sessions.tsv
• participant+session+runs.json
• participant+session+runs.tsv
• sessions.json
• sessions.tsv

4. Tools that currently expect only one row of participants files

I am more than happy to file an Issue for any repo missing interpretation of the session_id or other multiple-indexed column files. Thanks for pointing out those 4 with Claude. Those are big and important ones (MNE-BIDS, PyBIDS, NiBabies/NiPreps, and the validator itself), though I do have a hard time believing that LLM query without further investigating its query results myself.

On the close of this comment

I would really like to close more cans of worms than I'm opening here. How can we do that when the topic seems to be growing on this GitHub issue? Can we break this down into more actionable smaller conversations?