--- title: "Joining your own data" output: rmarkdown::html_vignette vignette: > %\VignetteIndexEntry{Joining your own data} %\VignetteEngine{knitr::rmarkdown} %\VignetteEncoding{UTF-8} --- This article shows how to join a table of your own, such as trait measurements or plot records, to a `taxify()` result when the two write species names differently. `add_data()` matches the table's names against the same backbones as the result and joins on the accepted taxon, so a synonym in the table still reaches its row. It ends with the joined table written to a file with `export_data()`. Installation is covered in [Get started](taxify.html). 1. **Match** the species list with `taxify()`. 2. **Join** a data frame or a file with `add_data()`, naming the species column with `species_col` or letting `add_data()` find it. 3. **Select** columns with `cols`, and settle repeated species. 4. **Export** the joined result with `export_data()`. ``` r library(taxify) ``` ## Joining a data frame The species list has five plants, matched against the Catalogue of Life (COL): ``` r species <- c( "Quercus robur", "Fagus sylvatica", "Reynoutria japonica", "Pinus sylvestris", "Betula pendula" ) res <- taxify(species, backbone = "col", verbose = FALSE) ``` The trait table holds specific leaf area (SLA) and maximum height. Its author wrote Japanese knotweed under the synonym *Fallopia japonica*: ``` r traits <- data.frame( taxon = c("Quercus robur", "Fagus sylvatica", "Fallopia japonica", "Pinus sylvestris", "Betula pendula"), sla = c(18.2, 24.1, 21.0, 8.0, 22.3), max_height_m = c(35, 40, 3, 30, 25) ) joined <- add_data(res, traits, species_col = "taxon") #> Matching 5 names from 'taxon' through COL backbone... #> Matching 5 names... #> add_data: 5 of 5 species matched (100.0%). 0 names in data unmatched. joined[, c("input_name", "accepted_name", "sla", "max_height_m")] #> input_name accepted_name sla max_height_m #> 1 Quercus robur Quercus robur 18.2 35 #> 2 Fagus sylvatica Fagus sylvatica 24.1 40 #> 3 Reynoutria japonica Reynoutria japonica 21.0 3 #> 4 Pinus sylvestris Pinus sylvestris 8.0 30 #> 5 Betula pendula Betula pendula 22.3 25 #> Sources: COL 2026.09 | cite() for full citations ``` `add_data()` matched the names in `taxon` against COL, the backbone the result records, and joined on the accepted taxon, so the knotweed values reach the *Reynoutria japonica* row. The message reports how many rows of the result received values and how many names of the table matched nothing. Every row of the result is kept, with `NA` where the table has no value; species only in the table are left out. A name with more than one accepted taxon behind it joins on the pick that `taxify()` would make, which can be the wrong taxon when the name was written without its author. [How names are matched](fuzzy-matching.html#names-with-more-than-one-accepted-taxon) shows how to check such names. ## Joining a file A file path takes the place of the data frame. The example writes the trait table to a temporary CSV file first, with the species in a column called `latin_name` and a column of site codes: The call gives no `species_col`, so `add_data()` matches the first ten names of each text column against the backbone and takes the column with the most matches, if at least half match: ``` r csv <- tempfile(fileext = ".csv") write.csv( data.frame(latin_name = traits$taxon, site = "A", sla = traits$sla), csv, row.names = FALSE ) from_file <- add_data(res, csv) #> Auto-detecting species column among 2 character columns... #> 'latin_name': 5/5 matched (100%) #> 'site': 0/5 matched (0%) #> Detected species column: 'latin_name' (100% match rate) #> Matching 5 names from 'latin_name' through COL backbone... #> Matching 5 names... #> add_data: 5 of 5 species matched (100.0%). 0 names in data unmatched. from_file[, c("input_name", "sla")] #> input_name sla #> 1 Quercus robur 18.2 #> 2 Fagus sylvatica 24.1 #> 3 Reynoutria japonica 21.0 #> 4 Pinus sylvestris 8.0 #> 5 Betula pendula 22.3 #> Sources: COL 2026.09 | cite() for full citations ``` The site codes match no backbone name, so the column holding names wins. For your own file, only the path changes, and `species_col` names the column when the guess fails. The file type is read from the extension: - `.csv`, `.csv.gz`, `.tsv` and `.tsv.gz`: delimited text. - `.xlsx`: an Excel workbook, which needs the openxlsx2 package. `sheet` and `start_row` select the sheet and the header row; left out, `add_data()` searches the sheets and the first 20 rows for the species column. - `.sqlite` and `.db`: a SQLite database, which needs DBI and RSQLite. `table` names the table. - `.vtr`: a table in the format of [vectra](https://gillescolling.com/vectra/), the package taxify reads backbones with. ``` r add_data(res, "bird_morphometry.xlsx", sheet = "measurements", start_row = 3) add_data(res, "traits.sqlite", table = "plant_traits", species_col = "species") ``` Other formats, such as `.parquet` or `.rds`, are read into a data frame first. ## Choosing columns `add_data()` joins every column except the species column. `cols` names the ones to keep: ``` r slim <- add_data(res, traits, species_col = "taxon", cols = "sla") #> Matching 5 names from 'taxon' through COL backbone... #> Matching 5 names... #> add_data: 5 of 5 species matched (100.0%). 0 names in data unmatched. setdiff(names(slim), names(res)) #> [1] "sla" ``` A column whose name the result already uses is added with the prefix `data_`, and the result's own column is unchanged: ``` r families <- data.frame( species = c("Quercus robur", "Fagus sylvatica"), family = c("Fagaceae", "Fagaceae"), leaf_area = c(45.2, 38.7) ) coll <- add_data(res, families, species_col = "species") #> Matching 2 names from 'species' through COL backbone... #> Matching 2 names... #> Prefixing colliding column(s) with 'data_': family #> add_data: 2 of 5 species matched (40.0%). 0 names in data unmatched. setdiff(names(coll), names(res)) #> [1] "data_family" "leaf_area" ``` ## Repeated species A table can hold the same species more than once: measurements from several sites, or two names that resolve to one accepted taxon. The check runs after matching, on the accepted taxon. Rows with identical values are merged, with a warning: ``` r same <- data.frame( species = c("Quercus robur", "Quercus robur", "Fagus sylvatica"), sla = c(18.2, 18.2, 24.1) ) merged <- add_data(res, same, species_col = "species") #> Matching 3 names from 'species' through COL backbone... #> Matching 3 names... #> Warning: 1 duplicate rows in data (same taxon, identical values) -- #> deduplicated. #> add_data: 2 of 5 species matched (40.0%). 0 names in data unmatched. ``` Rows with different values in any selected column stop the join, two `NA` values counting as equal, because `add_data()` cannot tell which value is right: ``` r differ <- data.frame( species = c("Quercus robur", "Quercus robur", "Fagus sylvatica"), country = c("AT", "DE", "AT"), sla = c(18.2, 21.5, 24.1) ) add_data(res, differ, species_col = "species", cols = "sla") #> Matching 3 names from 'species' through COL backbone... #> Matching 3 names... #> Error: #> ! 1 species in data resolved to the same accepted name but have different trait values. #> Examples: 'Quercus robur' ``` Averaging the rows before the join is one fix. When the rows belong to different groups, such as countries, `group_col` keeps them apart, with one column per group: ``` r by_country <- add_data(res, differ, species_col = "species", group_col = "country") #> Matching 3 names from 'species' through COL backbone... #> Matching 3 names... #> Tip: pipe into taxify_long() to reshape wide columns to long format. #> add_data: 2 of 5 species matched (40.0%). 2 groups. 0 names in data unmatched. by_country[, c("input_name", "sla_AT", "sla_DE")] #> input_name sla_AT sla_DE #> 1 Quercus robur 18.2 21.5 #> 2 Fagus sylvatica 24.1 NA #> 3 Reynoutria japonica NA NA #> 4 Pinus sylvestris NA NA #> 5 Betula pendula NA NA #> Sources: COL 2026.09 | cite() for full citations ``` `taxify_long()` turns the group columns into rows. ## Matching options `add_data()` matches the table's names with fuzzy matching, as `taxify()` does. `fuzzy_threshold` sets the tolerance, and `fuzzy = FALSE` accepts only exact matches, for a table whose names are already checked: ``` r add_data(res, traits, species_col = "taxon", fuzzy = FALSE) ``` ## Combining with published data `add_data()` and the `add_()` functions join on the same accepted taxon, so they can be chained in any order: ``` r both <- res |> add_zanne() |> add_data(traits, species_col = "taxon", cols = "sla") #> Matching 5 names from 'taxon' through COL backbone... #> Matching 5 names... #> add_data: 5 of 5 species matched (100.0%). 0 names in data unmatched. both[, c("accepted_name", "woodiness", "sla")] #> accepted_name woodiness sla #> 1 Quercus robur woody 18.2 #> 2 Fagus sylvatica woody 24.1 #> 3 Reynoutria japonica 21.0 #> 4 Pinus sylvestris woody 8.0 #> 5 Betula pendula woody 22.3 #> Sources: COL 2026.09, Zanne et al. 2014 2026.08 | cite() for full citations ``` ## Exporting the result `export_data()` writes the joined table to `.csv`, `.tsv`, `.xlsx` or `.vtr`, chosen by the extension: ``` r out <- tempfile(fileext = ".csv") export_data(joined, out) head(read.csv(out)[, c("input_name", "accepted_name", "sla")], 3) #> input_name accepted_name sla #> 1 Quercus robur Quercus robur 18.2 #> 2 Fagus sylvatica Fagus sylvatica 24.1 #> 3 Reynoutria japonica Reynoutria japonica 21.0 ``` A `.vtr` file written this way can be passed to `add_data()` in another project. ## How the join works 1. **Read** the table: a data frame as it is, a file by its extension. 2. **Find** the species column, from `species_col` or by matching samples of each text column. 3. **Match** the names against the backbones of the result, which `add_data()` reads from the result's metadata. Names that match nothing are counted in the message and left out. 4. **Check** for repeated accepted taxa: identical rows are merged, different rows stop the join. 5. **Join** on the accepted ID together with the backbone that issued it, since an ID means something only within its backbone. When the result and the table were matched by different backbones, the accepted name is the key instead. ## Where next - [Attaching published data](enrichments.html): the `add_()` functions and `add_trait()`. - [Recording what was used](taxify.html#recording-what-was-used): citing and locking the versions behind a result.