2_cleaning.Rmd

---
title: "Data exploration and cleaning"
output:
  html_document:
    df_print: paged
    toc: true
  word_document: default
  pdf_document: default
---

This notebook corrects the errors detected by [1_quality_check.Rmd](1_quality_check.Rmd), based on the feed back from the people who collected the data. Corrections are done within this script to ensure reproducibility.

The output is a clean kobo file that can be used for analyses.

## Get data, libraries and functions

Load required libraries:

```{r, warning=FALSE, message=FALSE}
library(tidyr)
library(dplyr)
library(utile.tools)
library(stringr)
library(ggplot2)
library(readr)
library(readxl)
```

Useful custom functions

```{r}
source("process_attached_files.R")
source("download_kobo_attachment.R")
source("recover_nocoincidence_files.R")

# not in
"%!in%" <- function(x, y)!('%in%'(x,y))
```

Get Kobo raw output data:

```{r}
kobo_output<-read.csv(file="International_Genetic_Indicator_testing_V_4.0_-_latest_version_-_False_-_2023-11-02-08-23-26.csv", sep=";", header=TRUE) %>%

## add  taxon column
mutate(taxon=(utile.tools::paste(genus, species, subspecies_variety, na.rm=TRUE))) %>%
    # remove white space at the end of the name
    mutate(taxon=str_trim(taxon, "right"))

```

## Filter tests and records marked as not approved

Filter out records which were marked as "not_approved" in the manual Kobo validation interface (this means country assessors determined the is something wrong with that particular record).

```{r}
# check if any species is flagged as "validation_status_not_approved"
kobo_output %>%
      filter(X_validation_status=="validation_status_not_approved")

# omit those records from data:
kobo_clean<- kobo_output %>%
            filter(X_validation_status!="validation_status_not_approved")

```

Filter out any sort of tests

```{r}
# select likely columns to say "test"
cols= c("name_assessor", "email_assessor", "genus", "species", "subspecies_variety",
         "scientific_authority", "common_name", "GBIF_taxonID", "NCBI_taxonID", "time_populations")

# check for "test" on any of them
kobo_clean %>% 
  filter(if_any(all_of(cols), ~ grepl("test", .)))  %>% 
  select(country_assessment, name_assessor, genus, species, X_uuid)

# filter them out of dataset
kobo_clean<- kobo_clean %>% 
              filter(if_any(all_of(cols), ~ !grepl("test", .)))  %>%
              filter(species != "test")

```

## Number of populations

### Change -999 to NA

In the form, -999 was used to mark taxa with unknown number of extant populations. This was used because answering the question was mandatory, so leaving it blank wasn't possible. We have to change -999 to NA.

For n extant populations:

```{r}
kobo_clean<- kobo_clean %>%
             mutate(n_extant_populations= na_if(n_extant_populations, -999))
```

For n extinct populations:

```{r}
kobo_clean<- kobo_clean %>%
             mutate(n_extint_populations= na_if(n_extint_populations, -999))
```

### Correct "negative populations"

Once -999 was replaced by NA there should be no negative number of populations (if they are, they are typos that need to be corrected).

Check for extant populations:

```{r}
kobo_clean %>%
      filter(n_extant_populations<0) %>%
      select(country_assessment, taxon, name_assessor, n_extant_populations, n_extint_populations)
```

Check for extinct populations:

```{r}
kobo_clean %>%
      filter(n_extint_populations<0) %>%
      select(country_assessment, taxon, name_assessor, n_extant_populations, n_extint_populations)
```

### Correct 999 populations

Show which species (if any) have 999 EXTINCT populations. **Should this be -999?**

Check for extinct populations:

```{r}
kobo_clean %>%
      filter(n_extint_populations==999) %>%
      select(country_assessment, taxon, name_assessor, n_extant_populations, n_extint_populations, end)
```

Check for extant populations:

```{r}
kobo_clean %>%
      filter(n_extant_populations==999) %>%
      select(country_assessment, taxon, name_assessor, n_extant_populations, n_extint_populations, end)
```

### Double check 0 (zero) extant populations

Show which taxa (if any) have 0 (zero) extant populations. **Is this correct? needs to be manually checked**

```{r}
kobo_clean %>%
      filter(n_extant_populations==0) %>%
      select(country_assessment, taxon, name_assessor, n_extant_populations, n_extint_populations, end)

```

According to the assessors feedback the data of the following taxa is correct. For example, there are 0 extant populations because it is regionally extinct or because it was imposible to estimate how many populations it has:

```{r}
ok_0_extant<-c("Hieracium sandozianum",
               "Charadrius alexandrinus",
               "Planorbella magnifica",
               "Heterelmis stephani")
```

Filtering out those taxa that are correct we should have an empty object, is not, the remaining taxa still need to be verified:

```{r}
kobo_clean %>%
      filter(n_extant_populations==0) %>%
      filter(taxon %!in% ok_0_extant) %>%
      select(country_assessment, taxon, name_assessor, n_extant_populations, n_extint_populations, end)

```

### Create an object with all "n pops" issues together

If there are any remaining taxa with weird number of populations put them together in a single object.

If everything is correct now, the object should be empity.

```{r}
check_n_pops <- kobo_clean %>% 
      # variables of interest
      select(country_assessment, name_assessor, taxon, n_extant_populations, n_extint_populations, end) %>%

      # same filters that discussed above
      filter(n_extant_populations<0 |
            n_extant_populations==0 | 
            n_extant_populations==999 | 
            n_extint_populations==999) %>%
      filter(taxon %!in% ok_0_extant) %>%

# add a column stating what needs to be checked:

       mutate(need_to_check="check number of extant or extint populations. Are 0 correct? should 999 be -999? are extant/extint confused?")
check_n_pops
```

## Correct GBIF ids

The quality check script flagged any records where the GBIF Id is =/= 7, because that is the most common length. Changes after the assessors checked their data:

Corrections requested:

```{r}
# Gymnobelideus leadbeateri 

kobo_clean[kobo_clean$taxon=="Gymnobelideus leadbeateri", "GBIF_taxonID"] 
kobo_clean[kobo_clean$taxon=="Gymnobelideus leadbeateri", "GBIF_taxonID"] <-2440054
kobo_clean[kobo_clean$taxon=="Gymnobelideus leadbeateri", "GBIF_taxonID"]  

# Zingel asper
kobo_clean[kobo_clean$taxon=="Zingel asper", "GBIF_taxonID"]  
kobo_clean[kobo_clean$taxon=="Zingel asper", "GBIF_taxonID"]  <- 2382117
kobo_clean[kobo_clean$taxon=="Zingel asper", "GBIF_taxonID"]  

# Miniopterus schreibersii
kobo_clean[kobo_clean$taxon=="Miniopterus schreibersii", "GBIF_taxonID"]  
kobo_clean[kobo_clean$taxon=="Miniopterus schreibersii", "GBIF_taxonID"]  <- 9796816
kobo_clean[kobo_clean$taxon=="Miniopterus schreibersii", "GBIF_taxonID"]  

# Toxolasma lividum
kobo_clean[kobo_clean$taxon=="Toxolasma lividum", "GBIF_taxonID"]  
kobo_clean[kobo_clean$taxon=="Toxolasma lividum", "GBIF_taxonID"]  <-157572593
kobo_clean[kobo_clean$taxon=="Toxolasma lividum", "GBIF_taxonID"]  

# Caladenia woolcockiorum
kobo_clean[kobo_clean$taxon=="Caladenia woolcockiorum", "GBIF_taxonID"]
kobo_clean[kobo_clean$taxon=="Caladenia woolcockiorum", "GBIF_taxonID"] <- 2841407
kobo_clean[kobo_clean$taxon=="Caladenia woolcockiorum", "GBIF_taxonID"]

# Phyllurus kabikabi
kobo_clean[kobo_clean$taxon=="Phyllurus kabikabi", "GBIF_taxonID"]
kobo_clean[kobo_clean$taxon=="Phyllurus kabikabi", "GBIF_taxonID"] <- 5843345
kobo_clean[kobo_clean$taxon=="Phyllurus kabikabi", "GBIF_taxonID"]

# Ambuchanania leuchbryoides
kobo_clean[kobo_clean$taxon=="Ambuchanania leuchbryoides", "GBIF_taxonID"]
kobo_clean[kobo_clean$taxon=="Ambuchanania leuchbryoides", "GBIF_taxonID"] <- 5792195
kobo_clean[kobo_clean$taxon=="Ambuchanania leuchbryoides", "GBIF_taxonID"]


# Phonotimpus talquian
kobo_clean[kobo_clean$taxon=="Phonotimpus talquian", "GBIF_taxonID"]
kobo_clean[kobo_clean$taxon=="Phonotimpus talquian", "GBIF_taxonID"] <- 9720487
kobo_clean[kobo_clean$taxon=="Phonotimpus talquian", "GBIF_taxonID"]

# Ecklonia radiata brevipes
kobo_clean[kobo_clean$taxon=="Ecklonia radiata brevipes", "GBIF_taxonID"]
kobo_clean[kobo_clean$taxon=="Ecklonia radiata brevipes", "GBIF_taxonID"] <- 3196095
kobo_clean[kobo_clean$taxon=="Ecklonia radiata brevipes", "GBIF_taxonID"]



```

Correct GBIF ids even if they look weird:

```{r}
ok_GBIF<-c("Caladenia woolcockiorum",
          "Leucopatus anophthalmus", 
          "Phyllurus kabikabi",
          "Phonotimpus talquian, Hypotaenidia sylvestris",
          "Chersobius signatus",
          "Hypotaenidia sylvestris",
          "Megascops gilesi",
          "Dubusia carrikeri")

## add the manually corrected too, in case they are != 7

ok_GBIF<-c(ok_GBIF,
           "Gymnobelideus leadbeateri",
           "Zingel asper",
           "Miniopterus schreibersii",
           "Toxolasma lividum",
           "Caladenia woolcockiorum",
           "Phyllurus kabikabi",
           "Ambuchanania leuchbryoides",
           "Phonotimpus talquian",
           "Anaxyrus williamsi")

```

### an object with all GBIF issues together

If there are any remaining taxa with weird GBIF ids put them together in a single object.

```{r}
check_GBIF <- kobo_clean %>%
              filter(nchar(GBIF_taxonID)>0, nchar(GBIF_taxonID)!=7) %>%
              filter(taxon %!in% ok_GBIF) %>%
  # show only relevant columns
            select(country_assessment, name_assessor, taxon, GBIF_taxonID, end) %>%
# add a column stating what needs to be checked:

       mutate(need_to_check="check the GBIF taxonID. Either it looks plain different, or has more or less than 7 digits (most ids are 7 digits long, and this isn't, it could be an exception, or a mistake).")
check_GBIF
```

## Taxon names

Genus, species and subspecies should be a single word, check if there are cases where it isn't. Only exception would be "var." or "subsp." in the subspecies_variety field:

```{r}
kobo_clean %>% 
  filter(grepl(" ", genus) | 
         grepl(" ", species) | 
         grepl(" ", subspecies_variety)) %>%
   filter(!grepl("var.", subspecies_variety)) %>%
   filter(!grepl("subsp.", subspecies_variety)) %>%
  # show only relevant columns
  # show only relevant columns
            select(country_assessment, name_assessor, taxon, genus, species, subspecies_variety, end)

```

Corrections requested:

```{r}
# Gypaetus Gypaetus barbatus
kobo_clean[kobo_clean$taxon=="Gypaetus Gypaetus barbatus", "species"]
kobo_clean[kobo_clean$taxon=="Gypaetus Gypaetus barbatus", "species"] <-"barbatus"
kobo_clean[kobo_clean$taxon=="Gypaetus Gypaetus barbatus", "taxon"] <-"Gypaetus barbatus"
kobo_clean[kobo_clean$taxon=="Gypaetus barbatus", c("species", "taxon")] 

# Gyps Gyps coprotheres
kobo_clean[kobo_clean$taxon=="Gyps Gyps coprotheres", "species"]
kobo_clean[kobo_clean$taxon=="Gyps Gyps coprotheres", "species"] <-"coprotheres"
kobo_clean[kobo_clean$taxon=="Gypaetus Gypaetus barbatus", "taxon"] <-"Gypaetus barbatus"
kobo_clean[kobo_clean$taxon=="Gypaetus barbatus", c("species", "taxon")] 


# Hippocampus Hippocampus capensis
kobo_clean[kobo_clean$taxon=="Hippocampus Hippocampus capensis", "species"]
kobo_clean[kobo_clean$taxon=="Hippocampus Hippocampus capensis", "species"] <-"capensis"
kobo_clean[kobo_clean$taxon=="Hippocampus Hippocampus capensis", "taxon"] <-"Hippocampus capensis"
kobo_clean[kobo_clean$taxon=="Hippocampus capensis", c("species", "taxon")] 


# Poicephalus Poicephalus robustus
kobo_clean[kobo_clean$taxon=="Poicephalus Poicephalus robustus", "species"]
kobo_clean[kobo_clean$taxon=="Poicephalus Poicephalus robustus", "species"] <-"robustus"
kobo_clean[kobo_clean$taxon=="Poicephalus Poicephalus robustus", "taxon"] <-"Poicephalus robustus"
kobo_clean[kobo_clean$taxon=="Poicephalus robustus", c("species", "taxon")] 

# Campylorhynchus rufinucha humilis, rufinucha, nigricaudatus, xerophilus, nicaraguae, castaneus, capistratus
kobo_clean[kobo_clean$taxon=="Campylorhynchus rufinucha humilis, rufinucha, nigricaudatus, xerophilus, nicaraguae, castaneus, capistratus", "subspecies_variety"]
kobo_clean[kobo_clean$taxon=="Campylorhynchus rufinucha humilis, rufinucha, nigricaudatus, xerophilus, nicaraguae, castaneus, capistratus", "subspecies_variety"] <-""
kobo_clean[kobo_clean$taxon=="Campylorhynchus rufinucha humilis, rufinucha, nigricaudatus, xerophilus, nicaraguae, castaneus, capistratus", "taxon"] <- "Campylorhynchus rufinucha"
kobo_clean[kobo_clean$taxon=="Campylorhynchus rufinucha", c("subspecies_variety", "species", "taxon")] 

# Icterus Icterus gularis gularis, tamaulipensis, yucatanensis, flavescens, troglodytes,  gigas
kobo_clean[kobo_clean$taxon=="Icterus Icterus gularis gularis, tamaulipensis, yucatanensis, flavescens, troglodytes,  gigas", "subspecies_variety"]
kobo_clean[kobo_clean$taxon=="Icterus Icterus gularis gularis, tamaulipensis, yucatanensis, flavescens, troglodytes,  gigas", "subspecies_variety"] <-""
kobo_clean[kobo_clean$taxon=="Icterus Icterus gularis gularis, tamaulipensis, yucatanensis, flavescens, troglodytes,  gigas", "species"]
kobo_clean[kobo_clean$taxon=="Icterus Icterus gularis gularis, tamaulipensis, yucatanensis, flavescens, troglodytes,  gigas", "species"] <-"gularis"
kobo_clean[kobo_clean$taxon=="Icterus Icterus gularis gularis, tamaulipensis, yucatanensis, flavescens, troglodytes,  gigas", "taxon"] <- "Icterus gularis"
kobo_clean[kobo_clean$taxon=="Icterus gularis", c("subspecies_variety", "species", "taxon")] 


# Melanerpes santacruzi grateloupensis, dubius, santacruzi, hughlandi, leei, turneffensis, pauper, insulanus, canescens
kobo_clean[kobo_clean$taxon=="Melanerpes santacruzi grateloupensis, dubius, santacruzi, hughlandi, leei, turneffensis, pauper, insulanus, canescens", "subspecies_variety"]
kobo_clean[kobo_clean$taxon=="Melanerpes santacruzi grateloupensis, dubius, santacruzi, hughlandi, leei, turneffensis, pauper, insulanus, canescens", "subspecies_variety"] <-""
kobo_clean[kobo_clean$taxon=="Melanerpes santacruzi grateloupensis, dubius, santacruzi, hughlandi, leei, turneffensis, pauper, insulanus, canescens", "taxon"] <- "Melanerpes santacruzi"
kobo_clean[kobo_clean$taxon=="Melanerpes santacruzi", c("subspecies_variety", "species", "taxon")] 

# Saltator atriceps atriceps, suffuscus, flavicrissus, peeti, raptor, lacertosus
kobo_clean[kobo_clean$taxon=="Saltator atriceps atriceps, suffuscus, flavicrissus, peeti, raptor, lacertosus", "subspecies_variety"]
kobo_clean[kobo_clean$taxon=="Saltator atriceps atriceps, suffuscus, flavicrissus, peeti, raptor, lacertosus", "subspecies_variety"] <-""
kobo_clean[kobo_clean$taxon=="Saltator atriceps atriceps, suffuscus, flavicrissus, peeti, raptor, lacertosus", "taxon"] <- "Saltator atriceps"
kobo_clean[kobo_clean$taxon=="Saltator atriceps", c("subspecies_variety", "species", "taxon")] 


# Ambuchanania leucobryoides / Ambuchanania leuchbryoides
# Should be Ambuchanania leucobryoides ('co' not 'ch' in species name)
kobo_clean[kobo_clean$taxon=="Ambuchanania leuchbryoides", "species"]
kobo_clean[kobo_clean$taxon=="Ambuchanania leuchbryoides", "species"] <-"leucobryoides"
kobo_clean[kobo_clean$taxon=="Ambuchanania leuchbryoides", "taxon"] <-"Ambuchanania leucobryoides"
kobo_clean[kobo_clean$taxon=="Ambuchanania leucobryoides", c("species", "taxon")] 


```

Correct:

```{r}
ok_taxon_name<-c("Coregonus albula morphotype trybomi",
                 "Glyptemys muhlenbergii Northern Population")
```

### an object with all taxon names issues together

If there are any remaining taxa with weird taxon names put them together in a single object.

```{r}
check_taxon_names <- kobo_clean %>% 
  filter(grepl(" ", genus) | 
         grepl(" ", species) | 
         grepl(" ", subspecies_variety)) %>%
   filter(!grepl("var.", subspecies_variety)) %>%
   filter(!grepl("subsp.", subspecies_variety)) %>%
   filter(taxon %!in% ok_taxon_name) %>%
  # show only relevant columns
            select(country_assessment, name_assessor, taxon, genus, species, subspecies_variety, end) %>%
       mutate(need_to_check="check genus, species or subspecies_variety, we are targeting to have single words in each field, except in the ifraspecific names, where 'var.' and 'subsp.' (only) would be accepted. Other details or taxonomic notes should be added in the comments.")
check_taxon_names
```

## Create a single file of pending issues for assessors review:

If there are still taxa that need to be checked for whatever reason, write a file enlisting them

```{r}
to_check<-full_join(check_n_pops, check_GBIF) %>% full_join(check_taxon_names) %>%
  # show columns in desired order:
select(country_assessment, name_assessor, taxon, need_to_check, n_extant_populations,
         n_extint_populations, GBIF_taxonID, genus, species, subspecies_variety, end)

to_check

# save file with a list of taxa if there are remaining issues, or with a message saying that all is done if not):

if(nrow(to_check)>0) {

write.csv(to_check, "kobo_output_tocheck_remaining_issues.csv", row.names = FALSE, fileEncoding = "UTF-8")
} else {
happy_message<-"congratulations! there are no more taxa with issues to be corrected. :)"
 write.csv(happy_message, "kobo_output_tocheck_remaining_issues.csv", row.names = FALSE, fileEncoding = "UTF-8")
}

```

## Change managment_unitis and other relevant methods to dispersal_buffer in Methods to define populations.

The method "management units" to define populations was interpreted differently by countries. For example, Belgium called "management units" a method in which they created a buffer polygons based on reported dispersal distances for each species around observation points, which are used as occurrence points. Polygons that overlap are fused together, and the resulting polygon, along with the points falling within its area, are considered a single population. However, for other countries "management units" refers to managed populations in the wild, sometimes enclosed, for instance as part of hunting schemes. Similarly, Japan used occurrence data in about 10-km grids and known dispersal distances to define populations as one or more grids, but called this a combination of management_units and geographic_boundaries.

Since we would be evaluating the effect of method on the indicators, we decided to leave management units into real management units and to create a new category, called "dispersal buffers", which would better reflect what countries did.

This is also reflected in the new versions of the Kobo form, where "dispersal buffer" has been added as an option.

Re-label data for Belgium:

```{r}
# select management_units that need to be changed to "dispersal_buffer" 
  # look for "managegment_units" in belgium and change that text to "dispersal_buffer" keeping any_text before or after
kobo_clean$defined_populations <- ifelse(kobo_clean$country_assessment == "belgium",
                                         gsub("management_units", "dispersal_buffer", kobo_clean$defined_populations),
                                         kobo_clean$defined_populations)

  
# Check resutls look as expected:
grep("dispersal_buffer", levels(as.factor(kobo_clean$defined_populations)), value=TRUE)

```

Re-label data for Japan. After manually reviweing the data and discussing it with Fumiko, we believe that all taxa fit better in the new category "dispersal_buffer", except for the following taxa, which we thin would be better labeled as geographic_boundaries:

```{r}

# geographic_boundaries species:
x<-c("Cryptocarya chinensis", "Polystichum piceopaleaceum", "Oxytropis campestris subsp. rishiriensi", "Sporobolus hancei")

# change selected species to geographic_boundaries
kobo_clean<-kobo_clean %>% mutate(defined_populations = ifelse(country_assessment == "japan" &
                                                         kobo_clean$taxon %in% x, # in the list then change to geo
                                    "geographic_boundaries", defined_populations)) %>%
  
# change the rest of japan species to "dispersal_buffer"
mutate(defined_populations = ifelse(country_assessment == "japan" &
                                    kobo_clean$taxon %!in% x, # if NOT in the list then change to dispersal_buff
                                    "dispersal_buffer", defined_populations))

# check data looks as expected
unique(kobo_clean[kobo_clean$country_assessment == "japan", "defined_populations"])

```

After checking the data with Viktoria she found that "geographic_boundaries" should be changed to "dispersal_buffer" for the species listed below:

```{r}
# species to change geo boundaries  to dispersal
x<-c("Bufotes viridis", "Pelophylax esculentus", "Pelobates fuscus", "Triturus cristatus", "Bombina bombina", "Bufo bufo", "Epidalea calamita", "Hyla arborea", "Lissotriton vulgaris", "Rana temporaria", "Rana arvalis", "Rana dalmatina", "Lepus timidus", "Erinaceus europaeus", "Muscardinus avellanarius", "Lacerta agilis", "Coronella austriaca", "Natrix natrix natrix", "Zootoca vivipara")

# change selected species to dispersal_buffr
kobo_clean$defined_populations <- ifelse(kobo_clean$country_assessment == "sweden" & kobo_clean$taxon %in% x,
                                         gsub("geographic_boundaries", "dispersal_buffer", kobo_clean$defined_populations),
                                         kobo_clean$defined_populations)

# check data looks as expected
filter(kobo_clean, country_assessment == "sweden" & taxon %in% x) %>% select(country_assessment, defined_populations, taxon)

```

## Save the clean koboutput version:

Remove from the clean version any remaining taxa with issues

```{r}
kobo_clean<-kobo_clean %>%
            filter(taxon %!in% to_check$taxon)
```

Export clean version

```{r}
write.csv(kobo_clean, "kobo_output_clean.csv", row.names = FALSE, fileEncoding = "UTF-8")
```

## Get and if needed correct population data provided in the templates

If information of more than 25 populations was be used to collect data for Ne \>500 indicator (Section 5 of the Kobo form), it is possible to use a template to upload data instead of using the kobo form. This section of the cleaning script processes those files to create a single clean object as expected to estimate indicator 1 (Ne \>500).

### Get files

Create directories to save files

```{r}
## original files

original_files_dir="processed_files/original_files"

  ## Create target directory overwriting previous content
  # Remove existing target directory if it exists
  if (dir.exists(original_files_dir)) {
    # Remove all files and subdirectories within the target directory
    files_to_remove <- list.files(original_files_dir, full.names = TRUE, recursive = TRUE)
    file.remove(files_to_remove)
    
    # Remove the target directory itself
    unlink(original_files_dir, recursive = TRUE)
  }
  # Recreate the target directory
  dir.create(original_files_dir, recursive = TRUE, showWarnings = FALSE)
  
## processed (and likely corrected) files:
processed_files_dir="processed_files/processed_files"

  ## Create target directory overwriting previous content
  # Remove existing target directory if it exists
  if (dir.exists(processed_files_dir)) {
    # Remove all files and subdirectories within the target directory
    files_to_remove <- list.files(processed_files_dir, full.names = TRUE, recursive = TRUE)
    file.remove(files_to_remove)
    
    # Remove the target directory itself
    unlink(processed_files_dir, recursive = TRUE)
  }
  # Recreate the target directory
  dir.create(processed_files_dir, recursive = TRUE, showWarnings = FALSE)
  
```

The url to the attachment file is avaialbe in the kobo_output under the variable `pop_tabular_file_URL`. This url could be used to download each of the attachments. For this, it is necessary to provide a kobo username with reading permissions and its password. To avoid showing passwords and usernames within this script, you have to create a `credentials.txt` file and save it in the working directory of this script. The `credentials.txt` should have ONLY two rows and one column (no headers). The first row should be the kobo user name and the second your password. For instance in the following example `myusername` is the username and `12334` is the password:

    myusername
    12334

The following code uses the credentials and the url stored in the kobo_output, for each of the records were population data was recorded using the tabular template and for which there is a url avaiable:

```{r}
# subset data
  tabular_records<-kobo_clean %>%
                filter(kobo_tabular=="tabular" & pop_tabular_file_URL!="") %>%
                select(country_assessment, taxon, kobo_tabular, X_uuid, pop_tabular_file, pop_tabular_file_URL, X__version__,
                       end, n_extant_populations)

# credentials
credentials<-read.delim("credentials.txt", header = FALSE)

for(i in 1:nrow(tabular_records)){
# log species
print(paste("Downloading data from record number", i, tabular_records$country_assessment[i], tabular_records$taxon[i], "file",  
      tabular_records$pop_tabular_file[i]))
  
# build file name with, country , record xuuid and original file name separated by __ (to parse easyly)
new_file_name  <- paste(tabular_records$country_assessment[i],
                        tabular_records$X_uuid[i], 
                        tabular_records$pop_tabular_file[i], sep= "__")

# download data
download_kobo_attachment(kobo_url = tabular_records$pop_tabular_file_URL[i],
                         local_file_path  = file.path(original_files_dir, new_file_name),
                         username = credentials[1,1],
                         password = credentials[2,1])
}

```

Check which files were downloaded:

```{r}
result_files<-list.files(original_files_dir)
result_files
```

If there were files stored as .xlsx, we need to transform those to txt files before proceeding.

```{r}
# get excel files, if any
excels<- grep(".xlsx", result_files, value = TRUE)
excels


if(length(excels)>0){
  # If excel files exist, read and save each one with same but as txt file
  
  for(i in 1:length(excels)){
  # Excel file path
  file_path <- file.path(original_files_dir, excels[i])
  
  # Read the first sheet of the Excel file as a text file
  data <- read_excel(file_path, sheet = 1, col_types = "text")
  
  # Save with same file name, changing the extension
  write.table(data, 
              file = file.path(original_files_dir, paste0(tools::file_path_sans_ext(excels[i]), ".txt")),
              sep = "\t", quote = FALSE, row.names = FALSE)
  }
}

```

Result files should only be txt or csv files

```{r}
result_files<-grep("\\.(txt|csv)$", list.files(original_files_dir), value = TRUE)
result_files
```

How many attached files we have (sucessfully downloaded and transformed frome excel, if needed)?

```{r}
length(result_files)
```

How many files were expected based on the kobo_output answers?

```{r}
expected<-kobo_clean %>% 
               filter(taxon %!in% to_check$taxon) %>% 
               filter(kobo_tabular=="tabular") %>% 
               select(country_assessment, name_assessor, taxon, X_uuid, map_populations_URL, end)

nrow(expected)
```

From which country(ies)?

```{r}
table(expected$country_assessment)
```

Which are the missing files?

```{r}
missing<-expected[expected$X_uuid %!in% str_match(result_files, "(?<=__).*?(?=__)"),]  # get the Xuud ie the characters after the first "__" and the second "__".
missing
```

### Check and, if needed clean attachment data.

The function [`process_attached_files()`](process_attached_files.R) processes the original attachment files to make them compatible with how population data is expected to estimate the Ne \>500 indicator. You can check the details in the function code.

Importantly, it checks the columns that **should be numbers**, but they may have been stored as characters because "," "-" "(" or other characters may have been typed. The function will try to correct this, which **may change the original data**. Please manually check any file flagged as transformed (log below) to ensure everything is correct.

These are the transformations that the function does and you should check:

**1) In numeric variables (Nc, Ne and their bounds): check if there are `()`, and remove them keeping only the value outside `()`.**

Example: `86 (95% CI)` will be transformed to: `86`

**2) In numeric variables (Nc, Ne and their bounds): convert `,` to `''` (nothing) IF more than 3 digits followed the `,` (ie we assumed `,` is separating thousands), OR convert `,` to `.` IF 2 digits followed the `,` (ie we assumed `,` is separating decimal points)**

Example: "3,00" "5000,000" "666" will be transformed to: "3.00" "5000000" "666"

**3) In numeric variables (Nc, Ne and their bounds): use as.numeric().**

Example: NcPoint expects ONLY numbers, an no other character. Therefore 400-600 stored in NcPoint will be transformed to NA. And there would be a warning saying "NAs introduced by coercion". In this example, if you want to keep "400-600" then you should have filled the column NcRange with in this case less_5000_by_much.

**4) NcType and NcRange should only have values from the specified categories, any other value will be changed to NA**

Example: \* NcRange values could only be 'less_5000', 'less_5000_bymuch', 'more_5000', 'more_5000_bymuch', 'range_includes_5000'. If the assessor types "600-8000" this will be transformed to NA.

-   NcType values could only be 'Nc_point', 'Nc_range'. If the assessor types "qualitative" it will be changed to NA.

**Other less serious edits, but that you should also know about, are the following:**

-   Population ids should be pop1, pop2.... Since people could have written all sort of things, we change the populationID of records to pop1, pop2, format instead of whatever people wrote.
-   If NcPoint data was provided NcType should exist, but if NcType was not provided, we set `NcType = Nc_point` for all pops
-   If NcRange data was provided NcType should exist, but if NcType not provided, we set `NcType = Nc_range` for all pops
-   If there is no Nc data, NcMethod and NcType should be NA, this was not the case so NA were introduced to replace the value.
-   People made mistakes when capturing the metadata (species name, GBIF_taxonID etc) in the population file, so it is not the same than the metadata stored in kobo_output. Because we trust more the data captured in kobo, we replaced ALL the following columns of ALL files with the variables stored in kobo_output: country_assessment, taxonomic_group, time_populations, taxon, scientific_authority, name_assessor, email_assessor, kobo_tabular, genus, species, subspecies_variety, year_assesment, GBIF_taxonID, NCBI_taxonID, multiassessment, national_taxonID.

Also, remember that excel files (if any), were transformed to .txt, so below we will only process the .txt and .csv files (see transformation above).

Please check the logs below and manually check the original and the processed files to ensure everything is correct.

```{r}
# files separated by "\t": australia, france, japan, sweden, most US, some belgium
df<-data.frame()
# numbers  reflect indexes of the files with the desired delim in result_files[i]
for(i in c(1:2, # australia
           5,11, # belgium
           16:17, # France
           18:20, # Japan
           21:23, # sweden
           24:28, 30:38) # US
    ){ 
  file<-paste0(original_files_dir,"/", result_files[i])
  cat(paste("##     processing element:", i, "\n",
            "   file:", file, " \n", " \n"))
  temp<-process_attached_files(file_path=file, kobo_output=kobo_clean, delim = '\t')
  
  # save processed file
  processed_file_path<-paste0(processed_files_dir,"/", "processed__", result_files[i])
  write.csv(temp, processed_file_path, row.names = FALSE)
  
  # attach object to df with all results
  df<-rbind(df, temp)
  
  cat(" \n") # white space before next text message
}

## files separated by ";" belgium 
for(i in c(3,4,6:10,12:15)){
  file<-paste0(original_files_dir,"/", result_files[i])
  cat(paste("##     processing element:", i, "\n",
            "   file:", file, " \n", " \n"))
  temp<-process_attached_files(file_path=file, kobo_output=kobo_clean, delim = ';')
  
  # save processed file
  processed_file_path<-paste0(processed_files_dir,"/", "processed__", result_files[i])
  write.csv(temp, processed_file_path, row.names = FALSE)
  
  # attach object to df with all results
  df<-rbind(df, temp)
  
  cat(" \n") # white space before next text message
}


## files separated by "," some Us
for(i in c(29)){
  file<-paste0(original_files_dir,"/", result_files[i])
  cat(paste("##     processing element:", i, "\n",
            "   file:", file, " \n", " \n"))
  temp<-process_attached_files(file_path=file, kobo_output=kobo_clean, delim = ',')
  
  # save processed file
  processed_file_path<-paste0(processed_files_dir,"/", "processed__", result_files[i])
  write.csv(temp, processed_file_path, row.names = FALSE)
  
  # attach object to df with all results
  df<-rbind(df, temp)
  
  cat(" \n") # white space before next text message
}


### all together
ind1_data_from_templates<-df
  
```

### Check final population data looks as it should.

Are columns looking good?:

```{r}
# columns look good?
head(ind1_data_from_templates)

```

These variables should be numeric:

```{r}
# should be numeric
class(ind1_data_from_templates$NcPoint)
class(ind1_data_from_templates$NcLower)
class(ind1_data_from_templates$NcUpper)
class(ind1_data_from_templates$Ne)
class(ind1_data_from_templates$NeLower)
class(ind1_data_from_templates$NeUpper)

```

Are the expected levels found in NcRange? The only categories should be: "less_5000" "less_5000_bymuch" "more_5000" "more_5000_bymuch" "range_includes_5000".

```{r}
# check levels
levels(as.factor(ind1_data_from_templates$NcRange))

```

Are the expected levels found in NcType? The only categories should be: "Nc_point" "Nc_range".

```{r}
# check levels
levels(as.factor(ind1_data_from_templates$NcType))
```

### Save indicator 1 data extracted from the tabular kobo templates into a file with indicator 1 data format:

```{r}
write.csv(ind1_data_from_templates, "ind1_data_from_templates.csv", row.names = FALSE)
```

## Session Info for reproducibility purposes:

```{r}
sessionInfo()
```