Ad-hoc spreadsheets can be appealing for managing some kinds of scientific data, but they allow bad practices like inconsistent types (e.g., string, integer, decimal) within individual columns, as well as open-ended categorical values (e.g., cell shapes). We have developed tools that infer LinkML schemas from spreadsheets, asserting types and enumerated lists that can be used to validate the dataset as it grows. Our tools can also associate OBO Foundry term identifiers with an enumeration's permissible values in the spirit of interoperability.
We have developed a method for inferring a semantic model from the most common data serializations, including delimited text files. In the case of CSV or TSV files, it is assumed that each row represents one instance of a single class, each column header represents one predicate P, and the contents of each cell represent the object of a P relationship with that instance. Patterns observed in the columns can be used to specify the range of the predicates: all numerical columns would specify a numerical range, columns with high entropy would specify a very open type, like String, and columns with extensive re-use of a limited set of textual values would be interpreted as categorical objects, perhaps the labels of classes that are defined elsewhere.
Methods are provided for proposing term identifiers with the same semantics as the observed labels. Specifically, the one term that best accounts for the longest span of cell contents is injected back into the inferred model. We provide support for running term mappings against locally downloaded files or over REST APIs, like that provided by OLS. We use SQLite for storing ontology content locally, so installing a database engine is not required. While the results are almost guaranteed to include some false positives and false negatives, the inclusion of lexical similarity metrics enables an expert reviewer to rapidly curate the mappings.
Inferred models are serialized in YAML, following the LinkML framework. This serialization can be converted to a wide variety of formats familiar to ontologists, like RDF, OWL and JSON-LD, can also be used to generate Python Dataclasses, and can drive validation, when converted to SHEX or JSON schema. The mappings from categorical cell contents to term identifiers can also be saved as SSSOM tables.
Our methods can be found in the turbomam/schemas-for-schemaless GitHub repository. There, we illustrate the usefulness of this schema inference and mapping workflow with a table of microbial traits from bacteria-archaea-traits/bacteria-archaea-traits. This repository aggregates over twenty sources of knowledge about bacterial and archaeal traits. One use case for the tabular outputs from this repository, such as condensed_species_NCBI.csv, is the construction of a knowledge graph such as KnowledgeGraph-Hub/kg-microbe. If the knowledge graph is to succeed in sustainably linking even more data sources, string values like “flask” the “cell_shape” column should be normalized to identifiers like MICRO:0000406, which bears the label “pear-shaped cell” and the exact synonym “flask”.
We start with a Makefile that: • clones the linkml/linkml-model-enrichment GitHub repository • clones bacteria-archaea-traits/bacteria-archaea-traits applies the infer_model.py script from linkml-model-enrichment/linkml_model_enrichment to the output/ condensed_traits_NCBI.csv file from bacteria-archaea-traits/output/ data file. An inferred schema in the LinkML format, serialized as a YAML file, results • downloads the rdftab executable • downloads ontologies that we expected to be applicable to the bacterial traits file: pato.owl and uberon.owl • creates an SQLite database and populates it with statements from the downloaded OWL files, using rdftab The remainder of the workflow is implemented in Python: • term ID and label pairs are quickly retrieved from the rdftab SQLite database • The inferred LinkML schema for condensed_traits_NCBI.csv is parsed, and the permissible values for the cell_shape column are extracted. • The permissible values are sanitized with lowercasing, whitespace normalization and replacement of several punctuation characters. • A first pass of mapping the cell_shape_enum permissible values to OBO foundry terms is performed by merging the sanitized values against the labels obtained from the rdftab SQLite database. • Sanitized cell_shape_enum permissible values that don’t match any labels from the rdftab SQLite database are submitted to the OLS search API • Additional metadata for each matching class is retrieved with a. sperate API call in order to determine what kind of relationship justified the match (exact synonym from Genbank, close synonym, etc.) • One best OLS hit for each Sanitized cell_shape_enum is retained, based on relevance and string similarity metrics. • The term mappings from the rdftab SQLite and OLS paths are merged, and any Sanitized cell_shape_enum permissible values that failed to map are reported • The mappings are injected back into the LinkML model 4. Results
condensed_traits_NCBI.csv consists of 172,325 taxon rows x 35 columns of identifiers, metadata and traits. infer_model.py automatically infers that the following columns take enumerable values: • cell_shape • data_source • gram_stain • metabolism • motility • rRNA16S_genes • range_salinity • range_tmp • sporulation • superkingdom infer_model.py can be forced to consider other columns as enumerable with the --enum-columns flag. The cell_shape values in condensed_traits_NCBI.csv are already well normalized and the sanitization step does not shrink the number of strings that require mappings: • bacillus • branced • coccobacillus • coccus • disc filament • flask • fusiform • irregular • NA • pleomorphic • ring • spindle • spiral
Four terms obtain mappings via merging with the rdftab database. (See Jupyter notebook for an
accounting [
Our method rapidly infers a schema from the bacterial and archaeal traits table and discovers columns that appear to take enumerable values. Two methods are provided and demonstrated for mapping the string representations of the enumerable values to terms from OBO Foundry ontologies. The selection of those two methods is informed by one’s tolerance for downloading ontologies to local storage vs depending upon a REST API. Visual inspection of the mappings suggests that they are all sound. Mapping failures can be attributed to misspellings in the input (e.g., branced instead of branched) or limitations placed on the search space. For example, our OLS search was limited to NCBITAXON, MICRO and OMP. Had FMA been included, the cell morphology FMA:70989 would have been discovered for the sanitized input ‘spindle’. Prioritizing quality over quantity, we were conservative in our selection of ontologies.
We have found that these methods work well for a variety of sources, including the INSDC
BioSample metadata collection [
Qualitatively speaking, the sensitivity and specificity of mapping gene names and symbols to OBO Foundry terms has not been as good as it is for microbial traits, sequence features, and environmental features. We are considering the addition of searches against gene or protein specific databases outside of the OBO Foundry, like UniProt or Entrez Gene. Whether we would take a local download approach or a REST API approach needs to be determined.
This work is supported in part by the Genomic Science Program in the U.S. Department of Energy, Office of Science, Office of Biological and Environmental Research (BER) under contract number DEAC02-05CH11231 (LBNL).