Omitting rows

When an L0 dataset is really valuable, but issues with the dataset (e.g. changes in temporal or spatial resolution across observations; edi.251.2) prevent conversion, the best option may be to convert a subset of the observations to the ecocomDP format. Follow these steps for omitting rows from the L0 dataset:

• Append message(paste0("This L1 dataset is derived from a version of ", source_id, "with omitted rows.")) below the create_eml() function call in the create_ecocomDP() function definition.
• After omitting rows, there may be residual columns that only applied to the now-omitted rows. You can remove these columns, but do not remove columns outside of this context.

Omitting tables

You may decide that only a subset of the data tables within an L0 dataset are well-suited to the ecocomDP format. In this case you have the option to omit entire data tables and only convert those that will fit the model.

When determining which tables to convert and which to omit, first identify which table(s) contain the “core” observation information. This will be the backbone of the intermediate “flat” table. Once the flat table is instantiated around the core observation, determine the shared keys by which to join the other L0 tables. The following types of variables are examples of common keys shared between data tables:

• Time (a column for year, month, day, date, or combination).
• Location (a column for site, plot, transect, or combination).
• Taxonomic information ( a species, scientific name, common name, or similar column).
• Other shared variables.

If you encounter tables that can’t be joined to the core observation information, possibly because they focus on a different time/location/taxon entirely, omit these problematic tables. Apply the following changes to the conversion script to highlight the table omission:

• In the “Join and flatten source data” section of the create_ecocomDP function definition, write a brief comment that highlights and justifies the decision to trim the L0 tables (i.e. # atmospheric_gas_concentrations table did not share a key with the bird_count table and was omitted).
• Append message(paste0("This L1 dataset is derived from a trimmed version of ", source_id, "with omitted tables")) below the create_eml() function call in the create_ecocomDP() function definition.

Do’s

• R is the only supported language.
• Every ecocomDP dataset must be fully created from the R script named “create_ecocomDP.R”; this is the conversion script.
• The only code in the conversion script should be the library() calls, the main function definition, and supporting function definitions outside of the create_ecocomDP() function.
• Explicitly state the dependencies of the function as library() calls for each (e.g. library(dplyr))
• Use the function name “create_ecocomDP” (i.e. create_ecocomDP <- function(...) {...} and only use the allowed set of arguments:
  • path - Where the ecocomDP tables will be written
  • source_id - Identifier of the source dataset
  • derived_id - Identifier of the derived dataset
  • url - The URL by which the derived tables and metadata can be accessed by a data repository. This argument is used when automating the repository publication step, but not used when manually publishing.
• Use a regex to refer to tables with names that are likely to change with future revisions.
• Convert missing value codes to NA. This is the ecocomDP standard.
• Whenever possible, programmatically insert information from the L0 metadata rather than manually copying and pasting. This facilitates the low effort transfer from a revised source dataset to the derived dataset, enabling automated maintenance.
• Comment your code liberally describing “what” and “why” so that future maintainers don’t have to spend as much time learning the nuances of the data.
• Use messaging (i.e. message()) and section headers (ctrl-shift-R in RStudio) at the beginning of each major code block to help maintainers with debugging, should it be needed.

Don’ts

• The rule of thumb is to never change the L0 dataset; only restructure/reformat:
  • See Value-Added Features for acceptable information to add.
  • Do not average or modify values. Users (e.g., synthesis working groups) will perform these calculations for their own needs.
  • Don’t change data types if it results in loss of data (e.g. coercing numeric characters interspersed with character codes, where coercion transforms the codes to NA; edi.115.2)
  • Don’t index by position, always index column by name (e.g. data$temp not data[[3]]). The order of columns may change in revised L0 datasets which will cause problems if indexed by position but not if indexed by name.
  • Don’t drop data from the L0. Even redundant information should not be removed.
• Never call rm(list = ls()) in the scripts. This will remove the global environment needed by automated maintenance routines.
• Don’t call the create_ecocomDP() function from within the conversion script. Otherwise the automated maintenance routine won’t be able to control the function call (i.e. input arguments and when it runs).

observation

Store the core observations being analyzed in this table. Observations must be linked to a taxon and to a location. Linking to ancillary observations is optional.

location

Store identifying information about a place (longitude, latitude, elevation) in this table. This table is self-referencing so that sites can be nested.

latitude and longitude

• Each row of the location table should contain a latitude and longitude value describing a point location. This information should be in the L0 dataset (e.g. metadata, data tables, or other entities). Sometimes there is geographic information at a different spatial scale than the listed locations. Follow these recommendations for determining the correct coordinates to provide:
  • The L0 dataset only has areas/polygons (instead of point locations) at a location. In such cases, use the centroid of each area (Figure 2A).
  • The L0 dataset only has point locations at finer spatial scale than a location. In such cases, use centroid of the constituent coordinates (e.g. centroid of transects within a site) (Figure 2B).
  • The L0 dataset only has point locations at a coarser level than a location. In such cases, transpose coarser coordinates to the location (e.g. each plot within a site would get the site’s coordinates) (Figure 2C).
  • The L0 dataset only has an area/polygon at a coarser level than a location. In such cases, use the centroid of the area/polygon as coordinates for the location (Figure 2D).
  • The L0 dataset has incomplete but accurate point locations for some locations. In such cases, the one-to-one matching of geographic coverage with some of the locations is best. The L1 user may be able to infer coverage to other adjacent locations with coordinates and elevations having NA values (Figure 2E).
  • Coordinates of location vary with each observation (e.g. specific coordinates where a plankton tow was done within the boundaries of an established/static station, edi.338.2) (Figure 2F).
    • First, take the average across the entire dataset of all observation coordinate corresponding to each location_name.
    • Second, in the location_ancillary table, list the exact coordinates for each date by including the datetime field. If this raises non-unique composite key issues (from validate_data()), then see the observation_ancillary section.

Figure 2. Mapping coordinates between spatial scales.

taxon

Store identifying information about a taxon in this table.

• Only core observation taxonomy belongs in the taxon table.
  • Store ancillary taxon information (i.e. species that were not the focus of the study) in the observation_ancillary table. E.g. In a community ecology survey of arthropods, the species of tree where the specimen was found is recorded in the “Substrate” column. The range of species in the substrate columns are not the taxonomic focus of the survey so they belong in the observation_ancillary table (e.g. edi.248.2).
• Taxa listed in the taxon table, and resolved to one of the supported authority systems (i.e. ITIS, WORMS, or GBIF), will have their full taxonomic hierarchy expanded, including any common names for each level. If you would like support extended to a different authority, then please open an issue in the project GitHub.
• If the taxon name in the L0 data tables is accompanied by a note (e.g. “Acanthaster planci (1m away)” vs.”Acanthaster planci”), list the verbatim name as-is in the taxon table. In this case, preserving the name avoids changing the L0 data and maintains emphasis of some feature that the L0 dataset creator wanted to highlight (edi.194.3).

dataset_summary

Store summary info about the L1 dataset in this table.

• To calculate temporal metrics of the dataset when resolution is only at year, add “01-01” (e.g. “1999-01-01”) to create a vector of dates so the calc_*() funcs can be used. Do not use this vector of modified and arbitrary dates in any of the datetime columns of the other tables, these should remain YYYY formatted.
• In the case that date is present for a fraction of observations but a coarser grain (e.g. YYYY) is present for all observations, create a composite date column using date for observations where it exists and coarser grain reformatted as YYYY-MM-DD elsewhere.

observation_ancillary

Store ancillary information about an observational event for context (e.g. plot or transect identifiers, measurement times, environmental conditions, field notes, etc.).

• Additionally, store location or core taxa variables in this table if they cause non-unique constraint validation issues in their own respective ancillary tables. This should be thought of as a temporary solution (see General Issues).

location_ancillary

Store additional information about a place that does not change frequently (e.g. lake area or depth, experimental treatment, habitat). Features that change frequently are more closely related to the observational event, and are thus kept in the observation_ancillary table. Ancillary observations are linked through the location_id, and one location_id may have many ancillary observations about it.

• If these site-specific variables change through time, use the datetime column to avoid creating non-unique composite keys.
• If site-specific variables change through time but the dates associated with the variable do not align with observation datetimes, round the date associated with the changing variable forward to the next observation date (e.g. edi.253.3).

taxon_ancillary

Store additional info about an organism that does not change frequently (e.g. trophic level). Features that change frequently are probably observations. Ancillary observations are linked through the taxon_id, which may have many ancillary observations about it.

• In the case where taxon-specific information changes through time (i.e. a different common name or description is used), make use of the datetime column to avoid creating non-unique composite keys.

variable_mapping

Link information from the variable_name columns of the observation, observation_ancillary, location_ancillary, and taxon_ancillary tables to external definitions in this table. See Ontologies and Vocabularies section for details.

General Issues

Other unforeseen issues with the L0 dataset may manifest themselves as you begin to create an ecocomDP (L1) formatted dataset. See the following suggestions for handling some common issues:

• Use the source (L0) dataset contact for help to resolve issues. Usually they will apply a fix on short notice. If not, then proceed the best you can.
• Contact ecocomDP project maintainers if you have any questions regarding the creation of an L1 ecocomDP dataset that were not covered in this document.
• Maintain an ongoing list of “considerations” for improving the L0 dataset to share with the L0 dataset author/manager. These are items that did not necessarily hamper production of the L1 dataset, but may improve the L0 dataset if addressed.
• When non-unique composite key issues arise in validation of the location_ancillary or taxon_ancillary tables:
  • First try to resolve this issue by utilizing the datetime column.
  • Try to get the creator/information-manager to fix the issue in the L0 dataset.
  • If this can’t be done in good time, a temporary solution is to move the problematic variable_name to observation_ancillary (which can handle everything).
  • This solution should be accompanied by a warning(), with a description of the issue, in the create_ecocomDP() function so that the next time this function runs, as a part of the maintenance routine, the logfile will prompt the maintainer to check for implementation of the L0 fix and adjust the create_ecocomDP() function accordingly.

Determining the Meaningful Level of Observation

• If there is only one level of observation, then it is the meaningful level of observation.
• If there are multiple levels of observation, only one should be deemed meaningful. Consider the following:
  • Always defer to any explicit statement of the meaningful level of observation from the L0 metadata.
  • If not explicitly stated in the L0 metadata, determine if there are changing habitats, experimental treatments, or elevations across any levels of observation (demonstrated in Figure 3).
    • If habitats or special variables do not change across any of the levels of observation, then the coarsest level of observation should be deemed meaningful.
    • If habitats or “special variables” change across a single one of the levels of observation, then that level should be deemed meaningful.
    • If habitats or special variables change across multiple levels, the finest resolution level where they change is deemed meaningful.

Using the Levels of Observation to assign location_id

We use the concept of meaningful level of observation to help assign location_ids:

• Group on the meaningful level of observation to assign location_id to the observations. The meaningful level of observation should go into the location table.
• If there are coarser levels of observation, they should also be grouped on when assigning location_id and go into the location table.
• If there are finer levels of observation, they should not be grouped on and they should go into observation_ancillary.
• If a habitat or other special variable corresponds to a physical location identifier (plot, subplot, transect, etc.), use the physical location identifier to group locations.
  • The physical identifier belongs in the location table and the corresponding habitat or special variable belongs in the location_ancillary table.

Determining the Meaningful Level of Observation (Visual Examples)

Figure 3. Varying experimental conditions change the meaningful level of observation in a study.

Determining the Frequency of Survey

• The L0 metadata is the first place to look for the Frequency of Survey. It could be explicitly stated in the title or methods as such:
  • “Annual Survey of Fish off the Florida Coast”
  • “Samples were collected once every two weeks”
• If the Frequency of Survey can not be determined from the L0 metadata, search for patterns in the L0 data:
  • Temporal variables (columns):
    • The presence or absence of temporal variables (a column of YEARs or MONTHs for instance) may suggest a yearly or monthly Frequency of Survey.
  • Use the lubridate package to transform dates into weeks, months, or years:
    • This may reveal patterns such as locations only being sampled once a year, once a month, or once a week.
  • Graphing locations over time:
    • A visual aid can help you identify trickier-to-spot patterns such as seasonal samplings or surveys that are roughly monthly but start and end in different months. Figure 4 demonstrates a variety of sampling patterns that may arise at each Frequency of Survey.
  • If you can not identify a single Frequency of Survey that is consistent throughout the entire study, attempt to determine the Frequency of Survey for each year of the study.
    • A study may start at one sampling frequency but change once or more over time. A study that starts as an annual survey could change to seasonal or monthly over time, or vice versa.
  • It is possible that the Frequency of Survey is not explicitly stated in the L0 metadata and can not readily be determined by patterns in the L0 data.
    • If this is the case, you should omit the event_id column entirely.

Frequency of Survey (Visual Examples)

Figure 4. Instances of Frequency of Survey on a timeline; Using Frequency of Survey to assign event_id.