Convert forest plot census data into a herbarium spreadsheet format

fp_herb_converter() converts a ForestPlots-formatted census sheet into a herbarium-ready spreadsheet by combining plot data (taxonomy, tags, subplot coordinates, field notes, Rainfor codes) with locality and collection metadata. It outputs a filled template in JABOT format by default, and also supports BRAHMS and custom templates (via column mapping).

This workflow is designed for voucher-based plot curation: you keep your inventory table as the single source of truth, and generate curator-ready exports reproducibly.

What the function does

Given two inputs:

a ForestPlots-format census file (fp_file_path)
an empty herbarium template (herb_file_path)

the function:

validates required arguments (directory, coordinates, admin divisions)
optionally checks if the point falls inside the stated country/majorarea
filters the census to collected individuals only (Collected column)
extracts collector/team and collection date from the sheet header (when present)
builds a herbarium table (one row per voucher record)
parses preliminary identification from Original determination
converts Rainfor codes (e.g., Flag1 and LI) into full-text descriptions
composes a standardized notes/description string in English, Portuguese, or Spanish
writes an .xlsx file inside a date-stamped output folder

Requirements

Inputs you need

A ForestPlots-format census sheet that includes at least:
- Voucher, Collected, Family, Original determination
- spatial fields used in notes (e.g., T1, X, Y, New Tag No)
- Rainfor fields used in notes (e.g., Flag1, LI, optionally Census Notes)
- a header row that contains a Date: dd/mm/yyyy style field (see pitfalls below)
An empty herbarium template spreadsheet:
- JABOT template (default) or
- BRAHMS template or
- any custom template (with a column map)

Packages used internally

The function reads/writes Excel (readxl, openxlsx) and can estimate elevation (geodata, terra) when alt = NULL.

Quick start: JABOT export (default)

Use this when your goal is a JABOT-ready spreadsheet for label generation and database ingestion.

fp_herb_converter(
  fp_file_path   = "data/forestplots_census.xlsx",
  herb_file_path = "templates/jabot_empty_template.xlsx",
  language = "en",
  herbarium_format = "jabot",
  country = "Brazil",
  majorarea = "Amapá",
  minorarea = "Oiapoque",
  protectedarea = "PARNA Montanhas do Tumucumaque",
  project = "Monitora – Componente Florestal",
  locnotes = "Permanent plot (maltese-cross), station MTQ-01.",
  lat = 1.23456,
  long = -52.34567,
  alt = NULL,                    # auto-extract elevation from lat/long
  add_census_notes = TRUE,
  validate_locality = TRUE,
  dir = "Results_filled_herbarium_sheet",
  filename = "forestplot_to_jabot"
)

Custom herbarium templates (column mapping)

Choose herbarium_format = "custom" if your herbarium uses a different set of column names (i.e., your template is not JABOT or BRAHMS).

Important: the current function body uses column_map, but column_map is not yet an argument in the function signature you shared.
To make the custom workflow work, add column_map = NULL to the function arguments and document it in roxygen.

1) Define a `column_map`

column_map is a named list that tells fp_herb_converter() which columns in your template should receive each piece of information. At minimum, your map should include fields for:

taxonomy: family, genus, sp1
collection identifiers: number, collector, addcoll
date: colldd, collmm, collyy
locality: country, majorarea, minorarea, protectedarea (optional), locnotes (optional)
coordinates/elevation: lat, long, alt
notes/remarks: notes (this is where the automatically generated descriptive text goes)

Here is a minimal-but-complete example:

column_map <- list(
  family = "family",
  genus  = "genus",
  sp1    = "species",
  number = "collector_number",
  collector = "collector",
  addcoll = "additional_collectors",
  colldd = "day",
  collmm = "month",
  collyy = "year",
  country = "country",
  majorarea = "state",
  minorarea = "municipality",
  protectedarea = "protected_area",
  project = "project",
  locnotes = "locality_notes",
  lat = "decimal_latitude",
  long = "decimal_longitude",
  alt = "elevation_m",
  notes = "occurrenceRemarks"
)

2) Run fp_herb_converter() with herbarium_format = “custom”

fp_herb_converter(
  fp_file_path   = "data/forestplots_census.xlsx",
  herb_file_path = "templates/custom_empty_template.xlsx",
  herbarium_format = "custom",
  language = "pt",
  country = "Brazil",
  majorarea = "Rio de Janeiro",
  minorarea = "Rio de Janeiro",
  lat = -22.967,
  long = -43.223,
  column_map = column_map,
  dir = "Results_filled_herbarium_sheet",
  filename = "forestplot_to_custom"
)

3) Tips for robust custom templates

Keep your template column names stable (avoid renaming columns between exports).
Put the template under version control (e.g., Git) so you can reproduce older exports.
Ensure the template contains all columns referenced in column_map—a typo in a column name will cause missing/empty fields (or errors).
Prefer plain ASCII column names in the template (no accents or special characters) to reduce cross-platform issues.
If your herbarium uses different conventions for administrative divisions (e.g., accents, abbreviations, alternative spellings), either harmonize them upstream or set validate_locality = FALSE until your naming is consistent.
Start by mapping only the core fields (family, genus, sp1, number, collectors, date, lat/long/alt, notes) and add optional fields incrementally.
Test with a small subset of vouchers first (a few collected rows) before exporting the full dataset.
After export, spot-check: (i) collector number, (ii) coordinates/elevation, (iii) taxon parsing from Original determination, and (iv) the generated notes text.

4) What gets written into `notes`

The column mapped as notes receives an automatically constructed descriptive text that combines plot measurements, protocol codes, and free-text observations into a single, curator-friendly sentence.

Depending on your settings and what is present in the ForestPlots sheet, fp_herb_converter() will assemble:

DBH (converted to cm; derived from the D column)
Tree condition codes from Rainfor (Flag1) converted to full phrases
(e.g., "q" → “with peeling bark” / “com casca solta/descamando”)
Light exposure codes (LI) converted to full phrases
(e.g., "5" → “crown completely exposed to vertical and lateral light…”)
Optional free-text field notes from Census Notes (if add_census_notes = TRUE)
Individual and position context, when available:
- tag number (New Tag No)
- subplot/arm identifier (T1)
- local coordinates (X, Y)

This is usually the most valuable field for herbarium workflows because it keeps standardized plot context and field interpretation of protocol codes attached to each voucher record.

--- title: "Convert forest plot census data into a herbarium spreadsheet format" format: html: toc: true --- `fp_herb_converter()` converts a **ForestPlots-formatted census sheet** into a **herbarium-ready spreadsheet** by combining plot data (taxonomy, tags, subplot coordinates, field notes, Rainfor codes) with locality and collection metadata. It outputs a filled template in **JABOT** format by default, and also supports **BRAHMS** and **custom templates** (via column mapping). This workflow is designed for voucher-based plot curation: you keep your inventory table as the single source of truth, and generate curator-ready exports reproducibly. --- ## What the function does Given two inputs: 1) a ForestPlots-format census file (`fp_file_path`) 2) an empty herbarium template (`herb_file_path`) the function: - validates required arguments (directory, coordinates, admin divisions) - optionally checks if the point falls inside the stated **country/majorarea** - filters the census to **collected individuals only** (`Collected` column) - extracts collector/team and collection date from the sheet header (when present) - builds a herbarium table (one row per voucher record) - parses preliminary identification from **Original determination** - converts Rainfor codes (e.g., **Flag1** and **LI**) into full-text descriptions - composes a standardized **notes/description** string in **English, Portuguese, or Spanish** - writes an `.xlsx` file inside a date-stamped output folder --- ## Requirements ### Inputs you need - A ForestPlots-format census sheet that includes at least: - `Voucher`, `Collected`, `Family`, `Original determination` - spatial fields used in notes (e.g., `T1`, `X`, `Y`, `New Tag No`) - Rainfor fields used in notes (e.g., `Flag1`, `LI`, optionally `Census Notes`) - a header row that contains a `Date: dd/mm/yyyy` style field (see pitfalls below) - An empty herbarium template spreadsheet: - **JABOT** template (default) *or* - **BRAHMS** template *or* - any custom template (with a column map) ### Packages used internally The function reads/writes Excel (`readxl`, `openxlsx`) and can estimate elevation (`geodata`, `terra`) when `alt = NULL`. --- ## Quick start: JABOT export (default) Use this when your goal is a JABOT-ready spreadsheet for label generation and database ingestion. ```r fp_herb_converter( fp_file_path = "data/forestplots_census.xlsx", herb_file_path = "templates/jabot_empty_template.xlsx", language = "en", herbarium_format = "jabot", country = "Brazil", majorarea = "Amapá", minorarea = "Oiapoque", protectedarea = "PARNA Montanhas do Tumucumaque", project = "Monitora – Componente Florestal", locnotes = "Permanent plot (maltese-cross), station MTQ-01.", lat = 1.23456, long = -52.34567, alt = NULL, # auto-extract elevation from lat/long add_census_notes = TRUE, validate_locality = TRUE, dir = "Results_filled_herbarium_sheet", filename = "forestplot_to_jabot" ) ``` ## Custom herbarium templates (column mapping) Choose `herbarium_format = "custom"` if your herbarium uses a different set of column names (i.e., your template is not JABOT or BRAHMS). > **Important:** the current function body uses `column_map`, but `column_map` is **not yet an argument** in the function signature you shared. > To make the custom workflow work, add `column_map = NULL` to the function arguments and document it in roxygen. ### 1) Define a `column_map` `column_map` is a named list that tells `fp_herb_converter()` **which columns in your template** should receive each piece of information. At minimum, your map should include fields for: - **taxonomy**: `family`, `genus`, `sp1` - **collection identifiers**: `number`, `collector`, `addcoll` - **date**: `colldd`, `collmm`, `collyy` - **locality**: `country`, `majorarea`, `minorarea`, `protectedarea` (optional), `locnotes` (optional) - **coordinates/elevation**: `lat`, `long`, `alt` - **notes/remarks**: `notes` (this is where the automatically generated descriptive text goes) Here is a minimal-but-complete example: ```r column_map <- list( family = "family", genus = "genus", sp1 = "species", number = "collector_number", collector = "collector", addcoll = "additional_collectors", colldd = "day", collmm = "month", collyy = "year", country = "country", majorarea = "state", minorarea = "municipality", protectedarea = "protected_area", project = "project", locnotes = "locality_notes", lat = "decimal_latitude", long = "decimal_longitude", alt = "elevation_m", notes = "occurrenceRemarks" ) ``` ### 2) Run fp_herb_converter() with herbarium_format = "custom" ```r fp_herb_converter( fp_file_path = "data/forestplots_census.xlsx", herb_file_path = "templates/custom_empty_template.xlsx", herbarium_format = "custom", language = "pt", country = "Brazil", majorarea = "Rio de Janeiro", minorarea = "Rio de Janeiro", lat = -22.967, long = -43.223, column_map = column_map, dir = "Results_filled_herbarium_sheet", filename = "forestplot_to_custom" ) ``` ### 3) Tips for robust custom templates - Keep your template **column names stable** (avoid renaming columns between exports). - Put the template under **version control** (e.g., Git) so you can reproduce older exports. - Ensure the template contains **all columns referenced in `column_map`**—a typo in a column name will cause missing/empty fields (or errors). - Prefer **plain ASCII** column names in the template (no accents or special characters) to reduce cross-platform issues. - If your herbarium uses different conventions for administrative divisions (e.g., accents, abbreviations, alternative spellings), either harmonize them upstream or set `validate_locality = FALSE` until your naming is consistent. - Start by mapping only the **core fields** (`family`, `genus`, `sp1`, `number`, collectors, date, `lat/long/alt`, `notes`) and add optional fields incrementally. - Test with a **small subset** of vouchers first (a few collected rows) before exporting the full dataset. - After export, spot-check: (i) **collector number**, (ii) **coordinates/elevation**, (iii) **taxon parsing** from *Original determination*, and (iv) the generated **notes** text. ### 4) What gets written into `notes` The column mapped as `notes` receives an automatically constructed descriptive text that combines plot measurements, protocol codes, and free-text observations into a single, curator-friendly sentence. Depending on your settings and what is present in the ForestPlots sheet, `fp_herb_converter()` will assemble: - **DBH** (converted to cm; derived from the `D` column) - **Tree condition codes** from Rainfor (`Flag1`) converted to full phrases (e.g., `"q"` → “with peeling bark” / “com casca solta/descamando”) - **Light exposure codes** (`LI`) converted to full phrases (e.g., `"5"` → “crown completely exposed to vertical and lateral light…”) - Optional **free-text field notes** from `Census Notes` (if `add_census_notes = TRUE`) - **Individual and position context**, when available: - tag number (`New Tag No`) - subplot/arm identifier (`T1`) - local coordinates (`X`, `Y`) This is usually the most valuable field for herbarium workflows because it keeps **standardized plot context** and **field interpretation of protocol codes** attached to each voucher record.