owned this note
owned this note
Published
Linked with GitHub
# 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**:
```r
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`:
```r
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.
```r
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
```r
# 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
```r
# 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
```r
userflora_dutch_to_scientificname <- function(table, dutchname) {
table %>%
filter(NaamNederlands == dutchname) %>%
collect %>%
pull(NaamWetenschappelijk)
}
userflora_dutch_to_scientificname(table = taxon, dutchname = 'Slanke sleutelbloem')
```
#### floris
```r
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 !!
```r
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
```r
# 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
```r
taxon %>%
left_join(rel_taxon_taxongroup, by = c("ID" = "TaxonID")) %>%
left_join(taxongroup %>% select(ID, Naam),
by = c("TaxonGroepID" = "ID")) %>%
count(Naam)
```
Sign in with Wallet
