dwc_mapping.Rmd

---
title: "Darwin Core mapping"
subtitle: "For: The (...) occurrences of "
author:
- Pieter Huybrechts
- Damiano Oldoni
- Lien Reyserhove
date: "`r Sys.Date()`"
output:
  html_document:
    df_print: paged
    number_sections: yes
    toc: yes
    toc_depth: 3
    toc_float: yes
#  pdf_document:
#    df_print: kable
#    number_sections: yes
#    toc: yes
#    toc_depth: 3
---

# Setup

```{r setup, include = FALSE}
knitr::opts_chunk$set(echo = TRUE, warning = FALSE, message = TRUE)
```

Load libraries:

```{r load libraries, message = FALSE}
library(dplyr)          # To do data wrangling
library(tidyr)          # To create tidy data
library(readr)          # To read data
library(stringr)        # To work with strings (chars)
library(purrr)          # TO work with functions and vectors
library(here)           # To find files
library(tidylog)        # To provide feedback on dplyr functions
library(magrittr)       # To use %<>% pipe
library(sf)             # To convert coordinate systems
library(janitor)        # To clean input data
library(digest)         # To generate hashes
library(rgbif)
library(sf)             # To convert coordinate systems
```

# Read source data

Create a data frame `input_data` from the source data:

```{r read raw data}

# raw data should be in memory from running fetch_data.R first


# check if the raw_data object exists
assertthat::assert_that(exists("raw_data"))

```

Rename `raw_data` to `input_data` so we can keep original values

```{r create input_data}
input_data <- raw_data
```

# Process source data

## Tidy data

Clean data somewhat:

```{r clean names and remove empty rows}
input_data <- 
  input_data %>% 
    remove_empty("rows") %>% 
    clean_names()
```

`global_id` is used as an unique identifier for each occurrence. Up to may 2024 we used `object_id` instead, however, it turned out this identifier wasn't stable and was recalulated when the database was updated or restored from backup. We have been assured `global_id` (a GUID) will remain stable in the future. 

This field should contain unique values only. and be mapped to the `occurrenceID`. `dossier_id` is the unique identifier for each sampling event. This will be the `eventID`

Check whether `object_id` is unique (value should be `0`), this should remain the case even if we aren't using it anymore.

```{r check for duplicate object ids}
assertthat::assert_that(anyDuplicated(input_data$objectid) == 0,
                        msg = "OBJECTID should never be duplicated")

```
Check whether `global_id` is unique.

```{r check for duplicate global ids}
assertthat::assert_that(anyDuplicated(input_data$global_id) == 0,
                        msg = "Global ID should always be unique")
```

Remove all rows for `Domein` = `Werken`

```{r remove Werken}
input_data %<>% filter(domein != "Werken")
```

## Define eventID and occurrenceID

```{r init event_id and occurrence_id}
input_data <- 
  input_data %>% 
    mutate(event_id = dossier_id) %>% 
    mutate(occurrence_id = global_id)

```

## Extract information from "waarnemingen"

The field `waarnemingen` is used to map `organismQuantity`, `organismQuantityType` and `occurrenceStatus`. This fields need to be cleaned before mapping.

```{r check contents of waarneming}
input_data %>% 
  group_by(waarneming) %>% 
  summarise(records = n()) %>% 
  arrange(waarneming)
```

First, we need to clean `Haard vastgesteld = 0; Waarneming onzeker = 1;`. This should be `Waarneming onzeker = 1;` (error in database):

```{r recode wrong values for waarneming}
input_data %<>% mutate(waarneming = recode(waarneming,
  "Haard vastgesteld = 0; Waarneming onzeker = 1;" = "Waarneming onzeker = 1;"))
```
Remove trailing whitespace from waarneming, so we can use `; ` as a way to detect multiple values for this field

```{r remove trailing whitespace from waarneming}
input_data <- 
  input_data %>% 
  mutate(waarneming = stringr::str_trim(waarneming))
```

Remove occurrences containing multiple type - value pairs information in column `waarneming` (patch until [#23](https://github.com/riparias/rato-occurrences/issues/23) is solved):

```{r remove records with multiple values for waarneming}
input_data %<>%
  filter(is.na(.data$waarneming) | 
           !str_detect(.data$waarneming, pattern = "; "))
```

Split into different columns: `waarneming_type` and `waarneming_kwantiteit`:

```{r split into waarneming_type and waarneming_kwantiteit}
input_data <- 
  input_data %>% 

    # First, separate `waarneming` into `waarneming_type` and `waarneming_kwantiteit`:
    separate(                                   
      col = waarneming,
      into = c("waarneming_type", "waarneming_kwantiteit"),
      sep = "\\s=\\s",
      remove = FALSE) %>% 

    # Clean waarneming_kwantiteit:
    mutate(waarneming_kwantiteit = str_remove(
      string = waarneming_kwantiteit,
      pattern = ";"))
```

Map `organism_quantity`:

```{r map organism_quantity}
input_data %<>% 
  mutate(organism_quantity = case_when(
    waarneming_type == "Vastgesteld (in m²)" |
    waarneming_type == "Vastgesteld (aantal)" |
    waarneming_type == "Secundair nest vastgesteld" |
    waarneming_type == "Secundair nest" |
    waarneming_type == "Embryonest vastgesteld" |
    waarneming_type == "Nest vastgesteld" |
    waarneming_type == "Primair nest" |
    waarneming_type == "Primair nest vastgesteld" ~ waarneming_kwantiteit,
    TRUE ~ ""
  )) 
```

Map `organism_quantity_type`:

```{r map organism_quantity_type}
input_data <-
  input_data %>% 
    mutate(organism_quantity_type = case_when(
      waarneming_type == "Vastgesteld (in m²)" ~ "square meter(s)",
      waarneming_type == "Vastgesteld (aantal)" ~ "individual(s)",
      waarneming_type == "Secundair nest vastgesteld" |
        waarneming_type == "Secundair nest" |
        waarneming_type == "Embryonest vastgesteld" |
        waarneming_type == "Nest vastgesteld" |
        waarneming_type == "Primair nest" |
        waarneming_type == "Primair nest vastgesteld"
      ~ "nest",
      TRUE ~ ""
    ))
```

Map `occurrence_status`:

```{r map occurrence_status}
input_data <-
  input_data %>%
    mutate(occurrence_status = case_when(
      waarneming_type == "Haard vastgesteld" |
      waarneming_type == "Nest vastgesteld" |
      waarneming_type == "Vastgesteld" |
      waarneming_type == "Vastgesteld (in m²)" |
      waarneming_type == "Vastgesteld (aantal)" |
      waarneming_type == "Secundair nest vastgesteld" |
      waarneming_type == "Secundair nest" |
      waarneming_type == "Beverdam vastgesteld (aantal)" |
      waarneming_type == "Primair nest" |
      waarneming_type == "Primair nest vastgesteld" |
      waarneming_type == "Embryonest vastgesteld" |
      waarneming_type == "Embryonest"
      
        ~ "present",
      
      waarneming_type == "Geen haard vastgesteld" |
      waarneming_type == "Niet vastgesteld" |
      waarneming_type == "Andere soort dan AH" |
      waarneming_type == "Geen waarneming gedaan" |
      waarneming_type == "Geen Aziatische hoornaar" |
      waarneming_type == "Geen nest vastgesteld" |
      waarneming_type == "Nest is al bestreden door derden" |
      waarneming_type == "Andere soort dan AH"
        ~ "absent",
      
      waarneming_type == "Waarneming onzeker" ~ "doubtful",
      
      TRUE ~ "present"
    ))
```

Screen mapping:

```{r check the current mapping}
input_data %>% 
  group_by(waarneming, organism_quantity, organism_quantity_type, occurrence_status) %>% 
  summarize(records = n()) 
```
## Extract information from "opmerkingen"

Some records have information regarding occurrenceStatus in the "opmerkingen" field

When `Dossier_Status` is `Opvolging`, old field values get copied. So even if "Waarneming" or "Actie" lead to a presence record, the Opmerkingen could still indicate a absence record. 

```{r map absences based on opmerkingen}
input_data <-
  input_data %>%
  mutate(
    occurrence_status = case_when(
      opmerkingen == "Geen planten gevonden" |
        opmerkingen == "Hoge waterstand. Geen planten zichtbaar" |
        opmerkingen == "Geen planten" |
        opmerkingen == "Geen plant meer" |
        opmerkingen == "Geen spoor gevonden" |
        opmerkingen == "Geen sporen gevonden!" |
        opmerkingen == "Geen ganzen gevonden" |
        opmerkingen == "Nest reeds door de brandweer behandeld. Geen beweging meer te zien."
      ~ "absent",
      
      .default = occurrence_status # in all other cases, leave it as is
    )
  )
```
## Drop test records based on "opmerkingen" and "opmerkingen_admin"

```{r drop test records}

# getting the records that have the word test in their comment fields, using
# word boundaries to avoid matching comments that just have test in the
# streetname
test_objects <-
  filter(
    input_data,
    stringr::str_detect(opmerkingen,
                        stringr::regex("\\btest\\b",
                                       ignore_case = TRUE)) |
      stringr::str_detect(opmerkingen_admin,
                          stringr::regex("\\btest\\b",
                                         ignore_case = TRUE))
  ) %>%
  pull(objectid)
# removing these records from the input dataset
input_data <- input_data %>% filter(!objectid %in% test_objects)

```


## Extract information from "materiaal_vast"

Information from `materiaal_vast` can be used for `samplingProtocol`

```{r check values for materiaal_vast}
input_data %>% 
  group_by(materiaal_vast) %>% 
  summarise(records = n()) %>% 
  arrange (records)
```

First split on ";" and separate on "="

```{r split and seperate materiaal_vast on seperators}
sampling_protocol <-
  input_data %>% 
    select(occurrence_id, materiaal_vast) %>% 

    # separate on ";" and split in different rows:
    separate_rows(
      "materiaal_vast",
      sep = ";") %>% 
  
    # remove rows for which materiaal_vast is empty:
    filter(materiaal_vast != "") %>% 
  
    # remove whitespaces:
    mutate(materiaal_vast = str_trim(materiaal_vast)) %>% 

    # Add materials column to see if separation was correct:
    left_join(
      y = select(input_data, occurrence_id, materiaal_vast),
      by = "occurrence_id"
    ) %>% 
    rename("materiaal_vast_full" = "materiaal_vast.y",
           "materiaal_vast" = "materiaal_vast.x")
    
```

Separate on " = " and split in different columns:

```{r split materiaal_vast into materiaal and kwantiteit}
sampling_protocol %<>% 
    separate(
      col = materiaal_vast,
      sep = "\\s=\\s",
      into = c("materiaal", "kwantiteit"))
```

remove "(aantal)" for easier mapping:

```{r remove aantal string from materiaal}
sampling_protocol %<>% 
   mutate(materiaal = str_remove(materiaal,"\\s\\(aantal\\)")) 
```

remove all rows for which \`kwantiteit = NA

```{r remove rows without a kwantiteit}
sampling_protocol %<>% 
  filter(!is.na(kwantiteit))
```

Resulting materials:

```{r check resulting materials}
sampling_protocol %>% 
  select(materiaal) %>% 
  group_by_all() %>% 
  summarise(records = n())
```

Translate to English (generate `protocol`):

```{r map materiaal to protocol via LUT}
sampling_protocol %<>% 
  mutate(protocol = recode(materiaal,
    "Andere" = "other",
    "Conibearklem" = "conibear trap",
    "Fuik" = "fike",
    "Grondklem" = "ground trap",
    "Klemvlot" = "raft trap",
    "Lokaasklem" = "bait trap",
    "Materiaal verdwenen/kapot" = "material lost/broken",
    "Slagnet" = "spring net trap",
    "Vangkooi" = "cage trap",
    "Vangnet" = "net trap",
    "Wildcamera" = "camera trap"))
```

Add `effort` to the dataset. This is the sampling effort and is based on the values in `kwantiteit`:

```{r create effort}
sampling_protocol %<>% 
  mutate(effort = case_when(
    kwantiteit == "0" ~ "",
    TRUE ~ paste(kwantiteit, protocol, sep = " ")))
```

Combine sampling efforts and methods per `occurrence_id`

```{r combine sampling effort with methods}
sampling_protocol <-
  sampling_protocol %>%
    group_by(occurrence_id) %>%
    summarise(sampling_protocol = paste(protocol, collapse = " | "),
              sampling_effort = paste(effort, collapse = " | "))
```

Now, the field `sampling_effort` contains some unwanted, repeated hashes. With this code, we remove them:

```{r remove repeated pipes from sampling_effort}
sampling_protocol <- 
  sampling_protocol %>%
  
  # Split by one or more consecutive vertical pipes, i.e. |, || or |||, and return a list
  mutate(sampling_effort_new = str_split(.data$sampling_effort, pattern = "\\|+")) %>% 
  
  # remove heading or trailing spaces in each element of each list
  mutate(sampling_effort_new = map(.data$sampling_effort_new, ~ str_trim(.))) %>% 
  
  # remove empty or NA elements from list and convert to character by using " | " as delimiter
  mutate(sampling_effort_new = map_chr(
    .data$sampling_effort_new, function(x) paste(x[x != "" & !is.na(x)], collapse = " | ")
    )
  ) %>% 

  # convert empty strings to NA. empty strings arise in the last step when pasting a character(0) vector, i.e. the input sampling_effort_raw is missing (NA) as in row 6
  mutate(sampling_effort_new = ifelse(.data$sampling_effort_new == "",
                                      NA_character_,
                                      .data$sampling_effort_new)
)

```

Merge `sampling_protocol` with `input_data`:

```{r merge sampling_protocol into input_data}
input_data %<>% left_join(
  y = sampling_protocol,
  by = "occurrence_id")
```

## Transform Lambert coordinates to WGS84

Coordinates in `x` and `y` are given in the Belgian Lambert system, they should be transformed to World Geodetic System 84 coordinate system

```{r transform to WGS84}
input_data_sf <- 
  input_data %>%
     st_as_sf(coords = c("x", "y"), crs = 31370) %>%
     st_transform(crs = 4326) 

x_y <- st_coordinates(input_data_sf)

input_data <- 
  input_data %>% bind_cols(x_y) %>% 
    rename(decimal_latitude = Y) %>% 
    rename(decimal_longitude = X)
```

## Extract scientific name from GBIF code

Extract scientific names from GBIF based on `gbif_code`.

First: correct some codes:

```{r overwrite certain gbif_codes with corrections}
input_data %<>%
  mutate(gbif_code = case_when(
    soort == "Waterteunisbloem" ~ 5421039,
    soort == "Rivierkreeft" & 
      (str_detect(waarneming, "Rode Amerikaanse rivierkreeft") | 
         str_detect(opmerkingen, "Amerikaanse")) ~ 2227300, 
    soort == "Mantsjoerese wilde rijst" ~ 10919373,
    TRUE ~ gbif_code
  )
)
```

Second: extract all unique gbif_codes from `input_data`.

```{r pull unique gbif_codes}
gbif_codes <- input_data %>% select(gbif_code) %>% distinct()
```

```{r query GBIF for scientific names}
scientific_names <- 
  map_dfr(gbif_codes$gbif_code, function(x) {
  if (!is.na(x)) {
    name_usage(key = x)$data %>%
    select(key, scientificName, canonicalName, rank)}
  else {tibble(scientificName = NA_character_, canonicalName = NA_character_)}
  }) 
```

Add scientific names to `input_data`

```{r add scientific names to input_data}
input_data <- 
  input_data %>% left_join(
    scientific_names,
    by = c("gbif_code" = "key"))
```

Clean `canonical_name` and `rank`:

```{r clean canonical_name and rank, manually correct canonicalName}
input_data <-
  input_data %>% 
    mutate(canonicalName_clean = case_when(
      soort == "Andere (soort vermelden):" & opmerkingen == "1 barberie gevangen, 1 verdwenen sinds vorige donderdag" ~ "Cairina moschata",
      soort == "Andere (soort vermelden):" & opmerkingen == "2 stuks roodwangschildpad" ~ "Trachemys scripta",
      soort == "Andere (soort vermelden):" & is.na(opmerkingen) ~ NA_character_,
      soort == "Halsbandparkiet:" ~ "Psittacula krameri",
      soort == "Ganzenactie" ~ "Anatidae",
      soort == "gedomesticeerde gans" & opmerkingen == "Brandganzen" ~ "Branta leucopsis",
      soort == "gedomesticeerde gans" & opmerkingen == "Muskuseend"  ~ "Cairina moschata",
      soort == "gedomesticeerde gans" & is.na(opmerkingen) ~ "Anatidae",
      TRUE ~ canonicalName))
```

Clean `rank`:

```{r clean rank}
input_data <- input_data %>% 
    mutate(rank_clean = case_when(
      rank == "KINGDOM" & is.na(canonicalName_clean) ~ NA_character_,
      rank == "KINGDOM" & canonicalName_clean == "Anatidae" ~ "family",
      rank == "KINGDOM" & canonicalName_clean == "Cairina moschata" ~ "species",
      rank == "KINGDOM" & canonicalName_clean == "Trachemys scripta" ~ "species",
      rank == "KINGDOM" & canonicalName_clean == "Psittacula krameri" ~ "species",
      TRUE ~ rank)) %>% 
    mutate(rank_clean = str_to_lower(rank_clean))
```

Create `kingdom` from `domein`:

```{r create kingdom from domein}
input_data <- 
  input_data %>% 
    mutate(kingdom = case_when(
      domein == "Dier" ~ "Animalia",
      domein == "Plant" ~ "Plantae"))
```

## Remove occurrences of undetermined taxa

Some occurrences cannot be linked (even manually) to a taxon, i.e. `canonicalName_clean` = `incertae sedis` or `NA`:

```{r check for records without taxa}
input_data %>%
  filter(is.na(canonicalName_clean) | canonicalName_clean == "incertae sedis")
```

We remove them:

```{r remove records without an identification}
input_data %<>%
  filter(!is.na(canonicalName_clean) & canonicalName_clean != "incertae sedis")
```

# Darwin Core mapping

```{r map to darwinCore}
dwc_data <- input_data
dwc_data %<>% transmute(
  type = "Event",
  language = "en", 
  license = "http://creativecommons.org/publicdomain/zero/1.0/", 
  rightsHolder = "RATO", 
  datasetID = "https://doi.org/10.15468/fw2rbx", 
  institutionCode = "RATO", 
  datasetName = "RATO - Daily operations commissioned by the province East Flanders, Belgium", 
  basisOfRecord = "HumanObservation", 
  eventID = event_id, 
  occurrenceID = occurrence_id,
  recordedBy = "RATO", 
  organismQuantity = organism_quantity, 
  organismQuantityType = organism_quantity_type,
  occurrenceStatus = occurrence_status,
  samplingProtocol = sampling_protocol,
  samplingEffort = sampling_effort,
  eventDate = as.POSIXct(laatst_bewerkt_datum), 
  countryCode = "BE", 
  municipality = gemeente,
  verbatimLatitude = y,
  verbatimLongitude = x,
  verbatimCoordinateSystem = "Lambert coordinates",
  verbatimSRS = "Belgian Datum 1972",
  decimalLatitude = round(decimal_latitude, digits = 5),
  decimalLongitude = round(decimal_longitude, digits = 5),
  geodeticDatum = "WGS84",
  coordinateUncertaintyInMeters = "30",
  scientificName = canonicalName_clean,
  kingdom = kingdom,
  taxonRank = rank_clean
)
```

Save to CSV:

```{r write out}
write_csv(dwc_data, here("data", "processed", "occurrence.csv"), na = "")
```