# INBO CODING CLUB
18 June, 2019

Welcome!

## Share your code snippet

If you want to share your code snippet, copy paste your snippet within a section of three backticks (```):

As an **example**:

library(tidyverse)

...

(*you can copy paste this example and add your code further down, but do not fill in your code in this section*)

Your snippets:

### START A CONNECTION TO DATABASE: BASICS

If you don't have a recent version of `inborutils`:

remotes::install_github("inbo/inborutils")

If connection with `inborutils` function `connect_inbo_dbase` doesn't work, try this code below. It should open a connection to `D0021_00_userFlora` INBO database.

my_connection <- DBI::dbConnect(odbc::odbc(),
                                driver = "SQL Server",
                                server = "inbo-sql07-prd.inbo.be", # or inbo-sql08-prd.inbo.be
                                port = 1433,
                                database = "D0021_00_userFlora", # or your database of interest
                                trusted_connection = "Yes")

### Challenge 1

#### hi

# connect to the database "D0021_00_userFlora"
con <- connect_inbo_dbase(database_name = "D0021_00_userFlora")
# connect to taxon table `tblTaxon`
taxon <- tbl(con, from = "tblTaxon")
class(taxon)
# Get the names of the columns in `taxon`
taxon %>% colnames
# Get preview first 10 rows of `taxon`
taxon
# Get the first 10 Dutch names in `taxon`
taxon %>%
  select(NaamNederlands)
# How many taxa are in `taxon` data.frame?
taxon %>%
  collect %>%
  nrow
# Get the first 10 Dutch names in `taxon` as a "normal" data.frame
taxon %>%
  select(NaamNederlands) %>%
  head(10) %>% 
  collect
# Get the scientific name of 'Geel schorpioenmos'
taxon %>% 
  filter(NaamNederlands == "Geel schorpioenmos") %>% 
  pull(NaamWetenschappelijk)

### Challenge 2

#### Cécile

# A function to retrieve the scientific name

userflora_dutch_to_scientificname <- function(table, dutchname) {
  table %>%
    filter(NaamNederlands == dutchname) %>% 
    select(NaamWetenschappelijk)
}

userflora_dutch_to_scientificname_extended <- function(table, dutchname) {
  table %>%
    filter(str_detect(NaamNederlands, dutchname)) %>% 
    select(NaamWetenschappelijk)
}

#### Anneleen

userflora_dutch_to_scientificname <- function(table, dutchname) {
  table %>% 
    filter(NaamNederlands == dutchname) %>% 
    collect %>% 
    pull(NaamWetenschappelijk)
    
}

userflora_dutch_to_scientificname(table = taxon, dutchname = 'Slanke sleutelbloem')

#### floris

userflora_dutch_to_scientificname <- function(table, dutchname) {
  table %>%
    filter(NaamNederlands == dutchname) %>%
    pull(NaamWetenschappelijk)
}

userflora_dutch_to_scientificname_extended <- function(table, dutchname) {
  table %>%
  filter(NaamNederlands %LIKE% (str_c("%", dutchname, "%"))) %>% 
  pull(NaamWetenschappelijk)
}

#### Ivy

Werken met `str_detect` geeft een ander resultaat dan werken met `%LIKE%`. `str_detect` is case sensitive, `%LIKE%` niet !!

userflora_dutch_to_scientificname_extended_strdetect <- 
  function(table, dutchname) {
    table %>%
      collect() %>%
      filter(str_detect(NaamNederlands, dutchname)) %>%
      select(NaamWetenschappelijk, NaamNederlands)
  }

userflora_dutch_to_scientificname_extended_like <- 
  function(table, dutchname) {
    table %>%
      filter(NaamNederlands %LIKE% 
               paste0("%", dutchname, "%")) %>%
      select(NaamWetenschappelijk, NaamNederlands)
  }

# check different output between both functions
taxon %>%
  userflora_dutch_to_scientificname_extended_strdetect("sterkranswier")
taxon %>%
  userflora_dutch_to_scientificname_extended_like("sterkranswier")

### Challenge 3

#### Cécile

# join taxon with taxon group
 
taxon_with_grp <- taxon %>% 
  left_join(rel_taxon_taxongroup %>% 
              select(TaxonID, TaxonGroepID), 
            by = c("ID" = "TaxonID")) %>% 
  left_join(taxongroup %>%  
              select(Naam,  ID), 
            by = c("TaxonGroepID" = "ID")) %>% 
  select(-TaxonGroepID) %>% 
  collect

taxon_with_grp %>% 
  count(Naam)

#### floris

taxon %>%
  left_join(rel_taxon_taxongroup, by = c("ID" = "TaxonID")) %>%
  left_join(taxongroup %>% select(ID, Naam),
             by = c("TaxonGroepID" = "ID")) %>%
  count(Naam)