01 Data checks

Author

Raphaël Nussbaumer

1 Introduction

This document runs the main checks on the source spreadsheet before any Darwin Core export is created. The workflow is intentionally simple: each section creates one intermediate object, shows the result directly, and keeps the code visible on the page.

The checks focus on a few practical questions:

  • Are event-level metadata fields consistent within each survey?
  • Are any counts or species names missing?
  • Do species names still match the local lookup and the AviList reference?
  • Are there duplicate records that should be excluded from export?

2 Basic statistics

This first step gives a compact summary of the current dataset by site and for the combined data. It is a quick way to confirm that the spreadsheet was read correctly before moving on to the detailed checks.

summary_table <- bind_rows(
  counts |> mutate(site_original = site),
  counts |> mutate(site_original = site, site = "Combined")
) |>
  group_by(site) |>
  summarise(
    n_survey = n_distinct(date, site_original),
    n_taxon = n_distinct(common_name),
    n_observation = n(),
    n_individual = sum(count, na.rm = TRUE),
    .groups = "drop"
  )

show_table_simple(
  summary_table,
  caption = "Summary of surveys, taxa, observations, and individuals."
)
Summary of surveys, taxa, observations, and individuals.
site n_survey n_taxon n_observation n_individual
Combined 306 121 9028 1115428
Mida Creek 145 85 3028 414489
Sabaki 161 112 6000 700939

3 Quality control

3.1 Assessment 1: One metadata record per event

Each survey event should have one consistent set of metadata such as start time, end time, coverage, method, and participants. This check groups records by date and site and flags events where one of these fields appears more than once.

event_metadata_check <- counts |>
  group_by(date, site) |>
  summarise(
    n_start_time = n_distinct(start_time),
    n_end_time = n_distinct(end_time),
    n_coverage = n_distinct(coverage),
    n_method = n_distinct(method),
    n_water = n_distinct(water),
    n_tidal = n_distinct(tidal),
    n_weather = n_distinct(weather),
    n_disturbed = n_distinct(disturbed),
    n_participants = n_distinct(participants),
    .groups = "drop"
  ) |>
  filter(if_any(starts_with("n_"), ~ .x > 1))

show_check_simple(event_metadata_check)
No issues found.

3.2 Assessment 2: Missing species name or count

The occurrence export needs both a species label and a count. This section separately checks for missing count values and missing common_name values so the source spreadsheet can be corrected before export.

missing_count_check <- counts |> filter(is.na(count))
missing_name_check <- counts |> filter(is.na(common_name))

show_check_simple(missing_count_check, "No missing counts found.")
No missing counts found.
cat("\n\n")
show_check_simple(missing_name_check, "No missing species names found.")
No missing species names found.
stop_if_issues(
  missing_count_check,
  c(
    "Missing counts found in the source data.",
    "x" = "Fix missing `count` values before exporting Darwin Core files."
  )
)

stop_if_issues(
  missing_name_check,
  c(
    "Missing species names found in the source data.",
    "x" = "Fix missing `common_name` values before exporting Darwin Core files."
  )
)

3.3 Assessment 3: Species name matches the lookup table

The raw spreadsheet uses common names, while the export attaches scientific names and taxon IDs through a lookup table. This check lists any common names that do not join to that lookup.

species_lookup_check <- counts |>
  distinct(common_name) |>
  mutate(
    common_name_key = stringr::str_to_lower(stringr::str_squish(common_name))
  ) |>
  left_join(species_lookup, by = "common_name_key") |>
  filter(is.na(scientific_name))

show_check_simple(species_lookup_check)
No issues found.
stop_if_issues(
  species_lookup_check,
  c(
    "Species names without a match in the lookup table were found.",
    "x" = "Fix the species lookup mismatch before exporting Darwin Core files."
  )
)

3.4 Assessment 4: Taxon IDs match the avilistr reference list

After the local species lookup has been joined, the next step is to confirm that the taxon identifiers still match the AviList reference used in the export script.

taxon_id_check <- counts |>
  distinct(common_name_key) |>
  left_join(species_lookup_join, by = "common_name_key") |>
  distinct(vernacular_name_source, taxon_id) |>
  filter(!is.na(taxon_id)) |>
  anti_join(avilist_lookup, by = "taxon_id")

show_check_simple(taxon_id_check)
vernacular_name_source taxon_id
Unidentified Shorebird shoreb1
Lesser/Greater Sand Plover y00648
Unidentified Terns tern1
Unidentified Egret Or Heron heron1

3.5 Assessment 5: Duplicate taxon records within one event

The export expects one count per taxon within a survey event. This check finds repeated date + site + common_name combinations so they can be reviewed before publication.

duplicate_observation_check <- counts |>
  count(date, site, common_name, name = "n_observation") |>
  filter(n_observation > 1)

show_check_simple(duplicate_observation_check)
date site common_name n_observation
2025-11-13 Mida Creek Common Greenshank 2

4 Records excluded from the export

This final table combines the records that would currently be excluded from export because they are incomplete or duplicated. It provides a short list of rows to fix in the source spreadsheet.

excluded_missing_count <- counts |>
  filter(is.na(count)) |>
  transmute(site, date, common_name, reason = "Missing count")

excluded_missing_name <- counts |>
  filter(is.na(common_name)) |>
  transmute(site, date, common_name, reason = "Missing common_name")

excluded_duplicate_observation <- counts |>
  add_count(date, site, common_name, name = "n_observation") |>
  filter(n_observation > 1) |>
  transmute(
    site,
    date,
    common_name,
    reason = "Duplicate date + site + common_name"
  )

excluded_export_records <- bind_rows(
  excluded_missing_count,
  excluded_missing_name,
  excluded_duplicate_observation
) |>
  distinct()

show_table_simple(
  excluded_export_records,
  caption = "Records temporarily excluded from the Darwin Core export."
)
Records temporarily excluded from the Darwin Core export.
site date common_name reason
Mida Creek 2025-11-13 Common Greenshank Duplicate date + site + common_name