A database record is not interchangeable with an organism, a sample or a gene. A study can contain many specimens, and a gene can have multiple transcripts and protein products. Sequence, expression, protein, structure and pathway resources answer different questions. Preserve species, genome assembly, accession/version, coordinate conventions and retrieval date alongside a record.
In a real project, select the organism and assembly before mapping variants to genes. Check whether a downloaded table reports gene IDs, transcript IDs, symbols or probes. Symbols can change or be shared. Joining on an ambiguous display label can duplicate rows or silently lose records. GEO, for example, distinguishes sample, series and platform records; these are different levels of metadata.
Model and assumptions
If feature f maps to several transcripts, a join produces several annotation rows for the same measured value. This is a representation of a relationship, not new biological replication. An eligible pathway background should be constructed from features that could have entered the analysis, after documenting the feature-to-gene mapping.
Check key uniqueness, unmatched records and row expansion before summarising. Define whether many features per gene will be combined, selected or retained; different choices answer different questions.
Worked example
The inputs below are hypothetical. Run the example, predict one change, then compare your prediction with the result. All executable code uses base R.
Show R code
features <-data.frame(gene=c("geneA","geneB","geneC"), effect=c(.5,-.2,.1))annotation <-data.frame(gene=c("geneA","geneA","geneB"),transcript=c("txA1","txA2","txB1"))joined <-merge(features, annotation, by="gene", all.x=TRUE)joined
geneA appears twice because it has two annotation records. geneC remains present but unmatched. Neither situation should be hidden by a convenient merge.
Separate records, evidence and biological entities
A sequence archive stores submitted measurement records; a reference resource organizes a selected assembly and annotation; a curated protein resource combines sequence with evaluated evidence. These are complementary roles. A cross-reference links records, but does not imply that all linked claims have the same evidence or review status.
Before querying, state the entity: a study, specimen, gene, transcript, protein product, sequence region or pathway. A gene symbol is convenient for display; an accession and version are usually better for traceable retrieval. Keep the original identifiers even after adding readable labels. Distinguish missing annotation from a measured absence of the molecule.
Mapping
Why several records arise
Analysis consequence
Gene to transcript
Alternative transcript models
One gene effect can appear in several annotation rows
Peptide to protein
Shared sequence
Protein identity may be ambiguous
Feature to metabolite
Candidate chemical assignments
Pathway membership may remain uncertain
Gene to orthologue
Duplication and evolutionary history
Cross-species transfer can be one-to-many
Coordinates are part of the identifier
A position is incomplete without chromosome, assembly and convention. One-based closed intervals include both endpoints; zero-based half-open intervals include the start and exclude the end. Thus a one-based interval 101–110 and a zero-based half-open interval 100–110 can describe the same ten bases.
The arithmetic is simple, but an unnoticed convention mismatch can shift overlap calculations. Conversion of convention is also different from transferring coordinates between assemblies. Assembly transfer requires a mapping and can leave regions unmatched or ambiguous.
Audit a mapping before using it
Report input keys, unique matched keys, unmatched keys and row expansion. Count unique biological entities for questions about entity overlap; count annotation rows only when rows are the intended unit. Avoid silently taking the first transcript returned by a database as the biologically relevant transcript.
The same gB is linked to two features, while f1 links to two genes. Deduplication cannot be solved by a universal rule: aggregation changes the measurement represented. Document the choice before enrichment or integration.
Search, retrieve and assess
For a public-study exercise, first read the experiment description and sample metadata. A downloadable expression matrix can lack the variables needed for the intended comparison. Check species, tissue, group definitions, biological replication and processing before considering its size.
Preserve the retrieval query, file checksum, reference versions and a local processing record. A later database update may improve annotation while changing results; reproducibility and current interpretation can require both the original mapping and a documented update.
Record-review challenge
A protein record cites experimental evidence for an enzymatic function in one species. What justifies assigning that function to a livestock gene? Evaluate the evolutionary relationship, sequence/domain evidence and experimental context. A cross-reference or similar name alone does not establish identical function.
Interpretation and scope
The fictional labels demonstrate cardinality rather than actual annotation. The same symbol in another species or assembly need not denote the same record. Biological assignment of a nearby variant to a gene is evidence to investigate, not proof of the causal gene. For actual record types, use the NCBI GEO documentation.
Exercises
Why did the row count increase?
What happens with an inner join?
Name four metadata fields needed for a genomic coordinate.
Suggested answers
geneA has a one-to-many mapping.
geneC disappears; this is an exclusion that should be reported.
Species, assembly, chromosome and coordinate convention; also record the annotation/version and feature identity.