Improved ways of working with tabular data

[cmungall]

If we assume a highly limited profile of LinkML:

• no multivalued
• a single class
• all ranges are literals/types

Then CSV/TSV loading is mostly trivial, because the CSV is isomorphic to the underlying objects. In practical terms we can feed the dict into python csvreader/writer and it will do the right thing.

Of course, even with this limited subset, there are multiple annoying edge cases due to lack of adhered to standards around CSVs, in particular

• conventions for missing data (Dealing with missing data #1994)
• escaping of newlines inside values

When we start to extend the profile, it gets harder to have predictable behavior. Even something as simple as allowing multivalued has annoying edge cases. We can pick an internal delimiter, such as |. This mostly works, modulo the missing data case above, and also assuming sensible escaping rules. There are edge cases where we might want a range to be an any_of multivalued and single valued, and there is no way to distinguish these cases.

any_of is problematic in general - consider trying to distinguish "1" from 1 in a CSV.

Things get harder when we start to allow ranges to be classes; sometimes there are "obvious" ways to flatten this but usually not.

Another approach with multivalued is to use the relmodel_transformer, which introduces new linking tables, but here let's assume that by "tabular format" we mean wide table denormalized where all observations fit into one table rather than linked relational tables.

For modern tabular databases and formats, there is no problem

• jsonl
• parquet
• arrow
• duckdb
• pickled data frames

allow for a richer tabular profiles, including mutlivalued and inlined object references, with clear non-YOLO distinctions between different base types.

An orthogonal concern is that there is a lack of standards for dataset-level metadata. A sensible paradigm is to follow sssom and include a schema-controlled yaml block in the header, but everyone does this differently.

But if we are limited to CSV/TSV, one approach is to treat this as a transformation problem. Different schemas can define different transforms for flattening data. See https://github.com/linkml/linkml-transformer. This is the most principled approach. However, it may be overkill for cases when the transformation is "obvious" (e.g. use a pipe to separate multivalued). The problem is when everyone's obvious transforms come together it can be hard to reason over the combination.

The current approach with the tsv loaders and dumpers is to try and do the "obvious" transforms, and it mostly works. It uses this under the hood: https://github.com/cmungall/json-flattener -- it will do things like use json serialization for nested objects, etc.

Can we do better than this? There is an argument for having a separate library just for tabular data, where we could escape from the "ismorphism assumption" underpinning the current runtime loaders/dumpers. This could also have a plugin architecture that would make it easy for people to use other tabular/columnar formats like parquet, dask, etc. It would allow for a certain amount of flexibility without requiring the use of linkml-transformers, with some reasonable defaults.

We may want to look into using flatten-tool as a drop in replacement for json-flattener (see #1031). This is schema guided and uses simple json-schema profiles to guide behavior.

[turbomam]

I jut wrote a some really crude Makefile targets for Kai Blumberg's Minimal Information about Food Components repo. They convert TSV example data files to YAML, which are then included in the standard examples validation step. It makes assumptions about the names of the TSV files, among other things.

At this time, all slots in all classes (besides the Container class) use atomic type or enum ranges. This is the same pattern that MIxS follows. MIFC doesn't use mutlivalued slots outside of Container but MIxS does.

I really want to work towards very fast and easy validation of tabular data in these projects, because I believe that's when the utility of the schemas will become most apparent to end users.

[turbomam]

see also

• Provide automation of CSV <-> YAML conversion and validation kaiiam/mifc#15

[cmungall]

FWIW, LinkML-store is using its own approach for unifying loading and parsing of frame-shaped data
https://github.com/linkml/linkml-store/blob/main/src/linkml_store/utils/format_utils.py

Although this would not have been necessary had we merged @sneakers-the-rat PR in earlier: linkml/linkml-runtime#305 (which provides a raw dict iterator for the different loaders)

We may also want to look at https://ibis-project.org/ suggested by a linkml community member, which provides a unified interface onto multiple backends.

[sneakers-the-rat]

i would be interested in this, i am doing my own hack on top of the pydantic generator to treat a special-cased class in the nwb schema as tables: https://github.com/p2p-ld/nwb-linkml/blob/ff77f0a2b8c95a46ee03ef81e35d8dd699feef15/nwb_linkml/src/nwb_linkml/includes/hdmf.py

and would way rather have a way of indicating that a given class expects tabular data, which as far as i can tell would just be a way of expressing that each of its slots/attributes are multivalued and have the same length. is there an elegant way we can think of expressing that? or should we just ducktype it?

The basic problem (i think) is that there are two orientations that tabular data could be modeled in, a class with slots s.t. each slot is multivalued and becomes a column, or a class with non-multivalued slots and each class instance is a row. In general I prefer the latter, but for the sake of expressiveness and compatibility with other schemas the former should be possible as well.

[turbomam]

2025-03-28 LinkML developer's meeting

• Ideally this would be maximally informed by the schema, as opposed to lots of command line or fields in a config file
• Different version of JSON schema have different options about truthiness (like 'yes')
• Might want to move away from JSON flattener
  • Old linkml-validate did use JSON flattener. New validator does not.
• Should we expect that the CSV is provided with SSSOM-like headers including a specification of delimiter, quotation rules, etc.

[tfliss]

This is an informative discussion, thank you @turbomam for pointing it out. I will try to restate some of what you, @cmungall and @sneakers-the-rat say above along with some thoughts from the perspective of working on the Pandera generator.

I agree that modern tabular databases/dataframes are not a problem (compared to CSV) in the sense that they are strongly typed and operations are well defined (executable). Conversion to/from CSV may best be treated as a read of simple string columns and then casting operations based on the model. I do believe multivalued and even nesting or association can also be handled by other transformations (split, unnest). I briefly looked at nesting and multiples in the context of MIxS containers with good results. This collab notebook Gist shows simple manual transforms on the MIxS example between the table form(s) from loading JSON and CSV. I do also have a LinkML Pandera validator running on these files in code that is not yet committed.

The multiple tabular representations @sneakers-the-rat speaks of correctly gets to a core issue. Though even in this short gist, there are more than two tabular representations, though they are transforms of each other. Any other popular database table arrangement would be another example, and there are contextual design decisions in favor of any one of them. It may be necessary to be able to represent many/all of them.

Having the configuration be part of the LinkML model sounds correct (as it is part of the dataframe schema), however that may lead to a proliferation of slightly different schemas. Also fine as long as transforming between the models forms is easy to do. I need to read more about LinkML transformers that @cmungall linked to. I would expect most dataframe operations could map to an analogous operation on a LinkML model.

These considerations will come up for the next stage of the Pandera generator for class ranges. I'm interested in expanding the example in the gist to cover the most needed cases.

[tfliss]

I'm also looking at an apparent 'impedance mismatch' between tabular schemas and the inlined-as-simple-dict convention.The dataframe libraries tend to prefer the more strongly typed list[struct] form and don't have a Union type that I'm aware of. The built in loaders and constructors also struggle with the convention. I have Pandera code that is passing the main compliance for inlined-as-simple-dict by removing the convention after loading data using a generic Object type. However I'll also circle back to check how linkml-store and flatten-tool approach this.

[turbomam]

Progress Update (2026-02-05)

Work addressing tabular data handling discussed here:

Completed:

• No truthy values are accepted by CSV validation #2580 → PR Extend Bool to accept common truthy/falsy values from CSV/TSV #3040 (merged) - Boolean coercion in CSV validation

In Progress:

• CSV/TSV loader does not split brackets-free, multivalued primitive slots (with pipe delimiter) #3041 → PR Add configurable list formatting for CSV/TSV serialization #3134 (open) - Configurable list formatting (list_syntax: plaintext, list_delimiter, list_strip_whitespace)

MIxS tracking: GenomicsStandardsConsortium/mixs#952 - central issue for MIxS multivalued field conventions

Related: #2581 (original feature request for configurable delimiters)

[turbomam]

Boolean Coercion Update (Issue #2580)

Following up on the progress update above - research on boolean truthy/falsy value handling for CSV/TSV is complete.

Agreed Approach

Accepted values for boolean slots:

Truthy	Falsy
true, True, TRUE	false, False, FALSE
yes, Yes, YES	no, No, NO
on, On, ON	off, Off, OFF
1	0

These align with YAML 1.1 conventions. Single-letter values (y/n, t/f) are not supported.

Key Design Principle

Schema-aware coercion only - Coercion happens only for slots with range: boolean.

Configurability

Similar to list formatting (#3134):

• Schema annotation: boolean_output
• CLI option: --boolean-output-format
• Default: true/false

See #2580 for details.

🤖 Generated with Claude Code

[cmungall]

We should not mix conventions, things are already chaotic enough with CSV formats. It looks like your agent is suggesting YAML 1.1 standard sentinel values. I'd avoid this, because this is different from de-facto CSV conventions (and additionally, they even went back on this with 1.2).

I would follow dataframe libraries in R and Python for deciding on default sentinel strings (sentinels should be overridable at the schema level, or CLI level)

https://pandas.pydata.org/docs/reference/api/pandas.read_csv.html

true_valueslist, optional
Values to consider as True in addition to case-insensitive variants of ‘True’.

false_valueslist, optional
Values to consider as False in addition to case-insensitive variants of ‘False’.

RDS is partially stricter, enforcing capitalization, and partially laxer, allowing single characters:

https://r4ds.had.co.nz/data-import.html

I don't think there is any answer that will satisfy anyone, but I suggest a union of RDF and pandas: case insensitive and have default sentinels {T, TRUE} or {F, FALSE}`

Additionally, "" should be coerced to null

[turbomam]

Update: Boolean value diversity in real NCBI BioSamples data

We're aligning PR #3144 with @cmungall's guidance above (default sentinels: case-insensitive {T, TRUE} / {F, FALSE}, with overridability). Before finalizing, we surveyed actual boolean slot usage in NCBI BioSamples (~42M records) to understand what real-world data looks like.

MIxS slots with range: boolean and their NCBI occurrence counts

Slot	Docs	MIxS example value
medic_hist_perform	21,742	1
smoker	20,499	yes
twin_sibling	1,286	yes
hysterectomy	1,277	no
reassembly_bin	?	no
x16s_recover	?	yes

Additionally, ~6 MIxS slots use string_serialization: '{boolean};...' for compound fields (e.g. gravidity: yes;due date:2018-05-11). Those are a separate problem.

Value distribution for medic_hist_perform (the richest boolean slot, 21,742 docs)

Value	Count	Category
missing	9,386	null-like
NA	3,196	null-like
not collected	1,700	null-like
Yes	1,681	boolean
yes	1,509	boolean
no	1,429	boolean
not applicable	596	null-like
Not applicable	446	null-like
TRUE	402	boolean
-	336	null-like
No	307	boolean
True	212	boolean
NOT APPLICABLE	135	null-like
Healthy	92	misuse
Unknown	26	null-like
medical history and physical exam	21	misuse
(clinical codes...)	...	misuse

Value distribution for hysterectomy (1,277 docs)

Value	Count	Category
NA	648	null-like
no	437	boolean
No	163	boolean
not applicable	28	null-like
missing	1	null-like

Observations

1. yes/no (various cases) is the dominant real-world boolean convention in MIxS NCBI data, followed by TRUE/True. This matches MIxS schema examples which all use yes/no.
2. Chris's recommended defaults (TRUE/FALSE/T/F) would miss the majority of actual boolean values in MIxS data without explicit configuration.
3. The null-like diversity is extreme: missing, NA, not collected, not applicable, Not applicable, NOT APPLICABLE, -, Unknown — this is a separate problem (relates to Dealing with missing data #1994).
4. Outright misuse exists: Healthy, clinical protocol names, etc. No sentinel set can handle this — it's an ETL/normalization concern.

Plan

• Implement Chris's defaults (TRUE/FALSE/T/F, case-insensitive, plus 0/1) as the out-of-box behavior
• Ensure the override mechanism (schema annotations + CLI flags) makes it easy for MIxS users to add yes/no
• Empty string "" to null coercion per Chris's request
• Will post smoker and twin_sibling value distributions shortly (querying now)

See also: MIxS issue #513 — "What values are acceptable for the MIxS {boolean} 'Value syntax'?" (open since 2022, no resolution)

[sneakers-the-rat]

taking a look at the example data and seeing yaml is one of the output formats, you are likely aware but always worth remembering that yaml bools are weird and TRUE, FALSE, T, F, yes, no, and all capitalizations (allcaps, all lower, first letter) are interpreted as boolean literals, but 0 and 1 are not, and esp if you are having compound fields in a single yaml value you might run into some weird problems unless you're always vigilant when loading and dumping.