In sidora-tools/sidora.core: Basic Interface for PANDORA (MPI-EVA DAG)

knitr::opts_chunk$set(eval = FALSE)

What is sidora?

• A collection of media and tools to interact with Pandora -- https://github.com/sidora-tools
• pandora-the-explorer: Blog posts exploring Pandora. https://sidora.eva.mpg.de/blog
• sidora: An R shiny app with predefined tables and plots. https://sidora.eva.mpg.de/sidora
• pandora2eager: A script to pump Pandora data into eager2. https://github.com/sidora-tools/pandora2eager
• ... a number of other things that didn't reach maturity
• sidora.core

What is sidora.core?

• An R package with a number of convenience functions to access Pandora data
• sidora.core is also the backbone of sidora (webapp) and pandora2eager (that's why it's called "core")

How to install sidora.core on your computer?

• sidora.core is not in the CRAN, so it can not be installed with install.packages()
• Instead you have to install it from GitHub
• Install the package remotes, which allows to install R packages from GitHub: install.packages('remotes')
• Install sidora.core: remotes::install_github("sidora-tools/sidora.core")
• On Windows you can only install sidora.core if you have installed the Rtools (not an R package!) https://cran.r-project.org/bin/windows/Rtools/rtools40.html

How to connect to the Pandora database server from your computer?

These steps are necessary every time you want to connect to the Pandora server (not the website!):

1. Connect to the institute's subnet via VPN (see the instructions in kbase)
2. Establish an SSH tunnel to the Pandora database server -- and keep it open!

ssh -L 10001:pandora.eva.mpg.de:3306 \
<your username>@daghead1

This means that the database server now listens on your computer at 127.0.0.1:10001.

How to connect to the Pandora database server from your computer?

We can actually send direct queries to the server there, e.g.

mysql --host=localhost --port=10001 \
--user=<secret> --password=<secret> pandora \
-e "select Sampled_Quantity from TAB_Sample"

But working with R and sidora.core might be more convinient for many applications

How to connect to Pandora with sidora.core?

1. Create a .credentials file somewhere on your computer
2. This file stores the relevant login information in a way sidora.core can understand

<host name of the database server>
<port of the database server>
<database username>
<database password>

How to connect to Pandora with sidora.core?

• The first two entries are what we enabled with the SSH tunnel to the Pandora server

127.0.0.1
10001
<database username>
<database password>

• The last two entries are the credentials of one specific user account, which has the necessary permissions to query data from Pandora
• It's not your username and password, but a specific user!
• We can not share this information publicly, but you can get it from Thiseas/James/Stephan/...

How to connect to Pandora with sidora.core?

1. Create a connection object in R with the .credentials file

con <- sidora.core::get_pandora_connection(
  "<path to your .credentials file>"
)

• This con object is what most functions in sidora.core need to run and retrieve information from the Pandora database

How to access Pandora tables with sidora.core?

There are two options:

1. Download the tables as data.frames into your computer's memory and run all computations there

sidora.core::get_df("TAB_Site", con) %>%
  dplyr::filter(site.Country == "Germany")

• The output of get_df() is directly compatible with the tidyverse
• All columns should have the correct data type
• All columns are renamed with an added table prefix:
  • Country -> site.Country
  • Creation_Date -> library.Creation_Date

How to access Pandora tables with sidora.core?

1. Send queries (dplyr -> SQL) to the database server and only download the results

sidora.core::get_con("TAB_Site", con) %>%
  dplyr::filter(Country == "Germany") %>%
  tibble::as_tibble()

• IMHO: Option 2 is for advanced users and specific applications
• A lot of the magic of the sidora.core package will not work with that: You know what you are doing.

How to download and merge multiple tables?

Multiple tables can be downloaded with get_df_list() and automatically joined with join_pandora_tables()

sidora.core::get_df_list(c(
  "TAB_Individual", "TAB_Sample"
), con = con) %>%
  sidora.core::join_pandora_tables()

How to download and merge multiple tables?

• This works, because Pandora is strictly hierarchical: Joins always behave the same, and every super-element is present

TAB_Site >
  TAB_Individual >
    TAB_Sample >
      TAB_Extract >
        TAB_Library >
          TAB_Capture >
            TAB_Sequencing >
              TAB_Raw_Data >
                TAB_Analysis

• This also allows to automatically bridge the gabs

sidora.core::make_complete_table_list(
  c("TAB_Site", "TAB_Capture")
)

How to replace lookup-table keys with real values?

Some values in Pandora are only ids referencing lookup tables (e.g. Worker).

sidora.core::get_df("TAB_Library", con) %$% 
  library.Worker %>% unique()

c(22, 4, 15, ...)

• We can decode them automatically

sidora.core::get_df("TAB_Library", con) %>% 
  sidora.core::convert_all_ids_to_values(con) %$% 
  library.Worker %>% unique()

c("Marieke van de Loosdrecht","Alissa Mittnik","Antje Wissgott", ...)

sidora-tools/sidora.core documentation built on May 4, 2024, 12:14 a.m.