--- title: "Joining custom data with add_data()" output: rmarkdown::html_vignette vignette: > %\VignetteIndexEntry{Joining custom data with add_data()} %\VignetteEngine{knitr::rmarkdown} %\VignetteEncoding{UTF-8} --- ```{r setup, include = FALSE} knitr::opts_chunk$set( collapse = TRUE, comment = "#>", eval = FALSE ) ``` This vignette shows how to attach trait data, occurrence records, or measurement tables from external sources to a `taxify()` result. External datasets rarely use the same names as the backbone. A CSV of leaf trait measurements might record *Pinus nigra* subsp. *laricio* while the backbone stores the accepted name as *Pinus nigra*, and a colleague's spreadsheet might list *Picea excelsa*, a synonym retired decades ago, where COL recognises *Picea abies*. A join on the raw species strings misses these rows and leaves `NA`s where values should exist. `add_data()` runs the external names through the backbone, resolves each to its accepted taxon, and joins on that, in a single pipe step. 1. **Match** the species list with `taxify()`. 2. **Read** the external data by passing a data.frame or a file path (`.csv`, `.tsv`, `.xlsx`, `.sqlite`, `.vtr`) to `add_data()`. 3. **Identify** the species column with `species_col`, or let `add_data()` detect it. 4. **Select** the columns to join with `cols`. 5. **Resolve** the duplicates and column name collisions `add_data()` reports. 6. **Export** the joined result with `export_data()`. ## Example ```{r, eval = TRUE, message = FALSE, warning = FALSE} library(taxify) ``` ### Joining a data.frame The most common case is a table of trait measurements already in the R session. Here a small table holds specific leaf area (SLA) and maximum height for five European tree species. ```{r} # Our taxify result species <- c( "Quercus robur", "Fagus sylvatica", "Picea abies", "Pinus sylvestris", "Betula pendula" ) result <- taxify(species, backbone = "col") # External trait data: note one synonym traits <- data.frame( taxon = c( "Quercus robur", "Fagus sylvatica", "Picea excelsa", "Pinus sylvestris", "Betula pendula" ), sla = c(18.2, 24.1, 6.5, 8.0, 22.3), max_height_m = c(35, 40, 50, 30, 25) ) # Join: "Picea excelsa" resolves to "Picea abies" through the backbone result <- result |> add_data(traits, species_col = "taxon") ``` `add_data()` takes the names from the `taxon` column, runs them through the same backbone(s) used in the original `taxify()` call, resolves each to its accepted taxon, and left-joins on it. *Picea excelsa* is a synonym of *Picea abies* in COL, so its SLA and height land on the *Picea abies* row even though the strings differ. The result gains two columns, `sla` and `max_height_m`. ### Joining from a CSV file A file path can be passed directly. `.csv` and `.csv.gz` files are read with vectra's CSV reader, and a `.csv.gz` path is decompressed transparently. ```{r} result <- taxify(species, backbone = "col") result <- result |> add_data("path/to/leaf_traits.csv") ``` If the file has a single obvious species-name column, auto-detection picks it up. When there are several plausible character columns, or the names sit in a column with an unusual name like `latin_binomial`, `species_col` removes the ambiguity. ```{r} result |> add_data("leaf_traits.csv", species_col = "latin_binomial") ``` ```{r} result |> add_data("global_leaf_traits.csv.gz", species_col = "species") ``` ### Joining from a TSV file Tab-separated files work the same way; `.tsv` and `.tsv.gz` files are read with `read.delim()`. ```{r} result |> add_data("leaf_traits.tsv", species_col = "species") result |> add_data("leaf_traits.tsv.gz") ``` ### Joining from an Excel file Spreadsheets are common in ecology, especially for hand-curated trait databases shared among collaborators. `add_data()` reads `.xlsx` files with the openxlsx2 package, which must be installed separately. ```{r} # install.packages("openxlsx2") # if not already installed result |> add_data("bird_morphometry.xlsx") ``` When `sheet`, `start_row`, and `species_col` are all left at their defaults, `add_data()` scans the workbook for the right combination. It tests each sheet and up to 20 candidate header rows, probing character columns against the backbone until it finds species names. This handles the common case of a spreadsheet with a title block, column descriptions, or notes above the data table. The scan reports what it found: ``` Scanning Excel layout... Detected: sheet 'measurements', header row 3, species column 'latin_name' (90% match rate) ``` Any combination of `sheet`, `start_row`, and `species_col` given explicitly skips that part of the detection: ```{r} # Specific sheet by name or number result |> add_data("bird_morphometry.xlsx", sheet = "measurements") result |> add_data("bird_morphometry.xlsx", sheet = 2) # Known header row (e.g., rows 1-2 are title/notes) result |> add_data("bird_morphometry.xlsx", start_row = 3) # All three specified: no scanning at all result |> add_data("bird_morphometry.xlsx", sheet = 1, start_row = 3, species_col = "latin_name") ``` ### Joining from a SQLite database A table in a SQLite database is read with vectra's SQLite reader, which depends on DBI and RSQLite. A single `.sqlite` or `.db` file can hold many tables, so the `table` argument is mandatory here, and omitting it raises an error. ```{r} # SQLite: requires DBI and RSQLite result |> add_data( "traits.sqlite", table = "plant_traits", species_col = "species" ) ``` A relational database of measurements maintained across projects can be joined this way without exporting to CSV first, and the backbone matching runs the same way as for any other format. ### Joining a vectra file Pre-built `.vtr` files (the columnar format taxify uses internally for backbone storage) can be passed directly. vectra reads them with near-zero overhead, because no parsing or type inference is needed. ```{r} result |> add_data("prebuilt_traits.vtr", species_col = "canonical_name") ``` This is mainly useful for sharing processed trait tables between team members or across projects: a `.vtr` file produced by one workflow can be reused in another without converting back through CSV. `export_data()` writes one: ```{r} # Save a taxify result (with enrichments) as .vtr result |> export_data("processed_traits.vtr") # A colleague can load it directly other_result |> add_data("processed_traits.vtr") ``` `export_data()` also writes `.csv`, `.tsv`, and `.xlsx` for tools outside R. ```{r} result |> export_data("for_excel_users.xlsx") result |> export_data("for_python.csv") ``` ### Other file formats Formats `add_data()` does not read directly (`.parquet`, `.rds`) can be read into a data.frame first and passed in that form. ```{r} my_data <- readRDS("legacy_traits.rds") result |> add_data(my_data, species_col = "sp") ``` ### Detecting the species column Without `species_col`, `add_data()` probes each character column of the external data and picks the one whose first 10 names match the backbone best (see [How the join works](#how-the-join-works)). ```{r} # Auto-detection in action traits <- data.frame( site = c("A", "A", "B", "B"), species = c("Quercus robur", "Fagus sylvatica", "Betula pendula", "Picea abies"), habitat = c("forest", "forest", "forest edge", "boreal"), sla = c(18.2, 24.1, 22.3, 6.5) ) # Three character columns: site, species, habitat # Only "species" will produce >50% backbone matches result |> add_data(traits) ``` Detection works well when the species column contains clean binomials and the other character columns hold obviously non-taxonomic strings (site codes, habitat descriptions, observer names). It can fail when column names are ambiguous, or when species names are heavily misspelled or given as common names, and then an explicit `species_col` avoids the error. ### Selecting columns with `cols` By default `add_data()` joins every column of the external data except the species column. When the external table has dozens of columns and only two or three are needed, `cols` names them. ```{r} # Full trait table with many columns big_traits <- data.frame( species = c("Quercus robur", "Fagus sylvatica"), sla = c(18.2, 24.1), max_height_m = c(35, 40), leaf_nitrogen = c(2.1, 2.4), wood_density = c(0.56, 0.58), seed_mass_mg = c(3500, 220), bark_thickness_mm = c(25, 8) ) # Only join SLA and wood density result |> add_data(big_traits, species_col = "species", cols = c("sla", "wood_density")) ``` `cols` can also leave out columns that would collide with existing ones. ### Column name collisions When the external data has columns named like columns already in the `taxify()` result, `add_data()` prefixes the incoming columns with `data_` and prints a message listing them. The existing columns of the result are never changed. ```{r} # The taxify result already has a "family" column # External data also has a "family" column (taxonomic family from a # different source) plus a "leaf_area" column external <- data.frame( species = c("Quercus robur", "Fagus sylvatica"), family = c("Fagaceae", "Fagaceae"), leaf_area = c(45.2, 38.7) ) result |> add_data(external, species_col = "species") # Output gains "data_family" (from external) and "leaf_area" (no collision) ``` If the conflicting column is not needed, leaving it out through `cols` avoids the rename. ### Duplicate species External datasets sometimes list the same species more than once: repeated measurements across sites, several literature sources compiled into one table, or subspecies that resolve to the same accepted species. `add_data()` separates two cases. Exact duplicates have identical trait values across the repeated rows. They are collapsed into a single row with a warning. ```{r} # Harmless: same species, same values (perhaps from two sites) dup_ok <- data.frame( species = c("Quercus robur", "Quercus robur", "Fagus sylvatica"), sla = c(18.2, 18.2, 24.1) ) result |> add_data(dup_ok, species_col = "species") # Warning: 1 duplicate rows ... deduplicated. ``` Conflicting duplicates differ in at least one of the selected columns between two rows that resolve to the same accepted taxon. The comparison runs column by column and treats two `NA` values as equal. Two rows for *Quercus robur* with SLA values of 18.2 and 21.5 conflict; so do two rows that agree on an SLA of 18.2 when one has `NA` for wood density and the other 0.56. The duplicates count as identical only when every selected column matches across all rows of a species. `add_data()` cannot decide which value is correct, so it raises an error that names the offending species. ```{r} # Conflicting: same species, different SLA values dup_bad <- data.frame( species = c("Quercus robur", "Quercus robur", "Fagus sylvatica"), sla = c(18.2, 21.5, 24.1) ) result |> add_data(dup_bad, species_col = "species") # Error: 1 species in data resolved to the same accepted name but have # different trait values. # Examples: 'Quercus robur' ``` The fix depends on the data. Within-species variation (measurements from different populations) can be aggregated before joining; data entry errors can be removed. When only some columns conflict, `cols` can select the non-conflicting subset so the join proceeds. ```{r} # Aggregate first, then join library(stats) dup_agg <- aggregate(sla ~ species, data = dup_bad, FUN = mean) result |> add_data(dup_agg, species_col = "species") ``` When the repeated rows are meant to stay apart, one per country or region, `group_col` names the grouping column and the output is pivoted to one column per group (`sla_AT`, `sla_DE`), with the conflict check applied within each group. The error message suggests `group_col` when a column looks like a country or region code. Duplicates are checked after backbone resolution, not on the raw names. If the external data lists both *Picea excelsa* and *Picea abies* with different SLA values, the two names resolve to the same accepted species and trigger the conflicting-duplicate error, because conflicting values for one join key cannot coexist. ### Controlling fuzzy matching `add_data()` resolves the external names with the same fuzzy matching as `taxify()`. Fuzzy matching catches typos and minor spelling differences, and can produce false matches between short or similar names. `fuzzy_threshold` sets how permissive it is, with lower values stricter, and `fuzzy = FALSE` turns it off. ```{r} # Strict: only very close matches result |> add_data(traits, species_col = "taxon", fuzzy_threshold = 0.1) # Exact matching only (no fuzzy) result |> add_data(traits, species_col = "taxon", fuzzy = FALSE) ``` Exact matching suits external data that is already well curated, where an approximate string match would risk attaching one species' values to another. ### Combining add_data() with enrichments `add_data()` sits in a pipe chain alongside the built-in enrichment functions. Custom data and pre-built enrichments join on the same accepted taxon, so they can be stacked in any order. ```{r} result <- taxify(species, backbone = "col") |> add_iucn() |> add_zanne() |> add_data(traits, species_col = "taxon") ``` Each step appends columns. The final data.frame holds the core `taxify()` output, IUCN conservation status, woodiness classification, and the custom SLA and height measurements, all aligned by accepted species. ## How the join works The pipeline inside `add_data()` has five steps: 1. **Read** the external data. File paths are dispatched by extension (`.csv`, `.csv.gz`, `.tsv`, `.tsv.gz`, `.xlsx`, `.sqlite`, `.db`, `.vtr`); data.frames pass through directly. Format detection relies on the file extension alone, so a misnamed file (e.g., a tab-separated file saved as `.csv`) produces a read error rather than a silent misparse. 2. **Identify** the species column, from `species_col` or by auto-detection. Auto-detection takes the first 10 rows of every character column and runs each sample through `taxify()` against the same backbone(s). The column with the highest match rate wins, provided it reaches 50%; otherwise `add_data()` stops and asks for `species_col`. Site codes, habitat labels, and observer names rarely match a backbone entry, so the species column tends to stand out. 3. **Match** the species names through the backbone(s) of the original `taxify()` call, which `add_data()` reads from the `taxify_meta` attribute `taxify()` attaches to its output, so they need not be given again. This produces an accepted taxon for each row of the external data. Fuzzy matching is on by default (`fuzzy`, `fuzzy_threshold`). Names that fail to resolve are dropped from the joinable pool, and their count appears in the summary message. If none resolve, no columns are added. 4. **Check for duplicates.** Rows of the external data that resolve to the same accepted name are compared. Exact duplicates (identical values in all selected columns) are collapsed with a warning; conflicting duplicates raise an error. 5. **Left join** on the accepted taxon. The key is the `accepted_id` together with the backbone that issued it, since backbone IDs are bare integers in most backbones and mean nothing outside the one they came from. Where the result and the external data were matched by different backbones, the accepted name is the key instead. Every row of the result with a match receives the trait columns, rows without a match get `NA`, and colliding column names are prefixed with `data_`. The join keeps every row of the original result. Species present in the external data but absent from the result are ignored. A summary message reports how many species were matched and how many names in the external data could not be resolved through the backbone.