Dataset description

[ypkoshka]

Thank you so much for your guidance on how to connect to Postgres database and upload the data (#30 (comment)). Here is a follow-up question: would it be possible to upload the dataset description along with the dataset? I know one can manually enter the description once the data is uploaded but I wonder whether it would be possible to have automatically populated when the data is being uploaded? If so, where would it be stored? Should it be stored in the dataset as a separate field? Could you please point to the R code which deals with the data description storage / upload?

[vnijs]

When you register the data.frame you can add a description in markdown format. I'd suggest reading it from a markdown file using readLines

http://radiant-rstats.github.io/radiant.data/reference/register.html

add_description <- function(df, md = "", path = "") {
  if (path != "") {
    md <- readLines(path) %>% paste0(collapse = "\n")
  } else if (md == "") {
    md <- "No description available"
  }
  set_attr(df, "description", md)
}

[ypkoshka]

Thank you so very much for your prompt response! I will try it out

[ypkoshka]

Hi Vincent, here is my code:

library(DBI)
library(gsubfn)

con <- dbConnect(
  RPostgres::Postgres(),
  user = Sys.getenv("db_userid_x"),
  host = "db_host_id",
  port = 5432,
  dbname = "postgres",
  password = Sys.getenv("db_pwd_x")
)

df_spec_study <- dbSendQuery(con, "SELECT 'SDY1' AS study_specified;")
specstudy <- as.character(dbFetch(df_spec_study))

experiment <-fn$dbGetQuery(con,"SELECT experiment.study_accession, expsample.experiment_accession, experiment.name, 
               experiment.measurement_technique, expsample.result_schema, lk_result_schema.result_table, count(*) 
               AS number_expsamples, 'experiment' AS tree_id
               FROM madi_dat.expsample
               LEFT OUTER JOIN madi_dat.experiment ON experiment.experiment_accession = expsample.experiment_accession
               LEFT OUTER JOIN madi_dat.lk_result_schema ON lk_result_schema.result_schema = expsample.result_schema
               WHERE experiment.study_accession = '$specstudy' GROUP BY experiment.study_accession, 
               expsample.experiment_accession, expsample.result_schema, experiment.name, 
               experiment.measurement_technique, result_table
               ORDER BY experiment.study_accession, expsample.experiment_accession, expsample.result_schema, 
               experiment.name, experiment.measurement_technique, result_table;")


add_description <- function(df, md = "", path = "") {
  if (path != "") {
    md <- readLines(path) %>% paste0(collapse = "\n")
  } else if (md == "") {
    md <- "No description available"
  }
  set_attr(df, "description", md)
}

 
add_description(experiment, path = "C:/R/R-4.2.0/library/radiant/app/desc/expdescr.md")
register("experiment")

And here are the errors I get:

> runApp('C:/R/R-4.2.0/library/radiant/app')
Warning in result_create(conn@ptr, statement, immediate) :
  Closing open result set, cancelling previous query
Warning in readLines(path) :
  incomplete final line found on 'C:/R/R-4.2.0/library/radiant/app/desc/expdescr.md'

Listening on http://127.0.0.1:5709
The `name` provided ('keyboard-o') does not correspond to a known icon
Warning in data(list = dn, package = "radiant.data", envir = environment()) :
  data set ‘experiment’ not found

Would you have any suggestions re: what the issue is? I'm not sure why R is trying to reuse 'con' which is the connection to the postgres database... Should we remove con altogether once the data frame experiment is created?

Thank you so much!
PS I edited this post as I fixed the invocation of the function

[vnijs]

What happens when you run the query? Does the "experiment" DataFrame exists What does head(experiment) show?

[ypkoshka]

Thank you so very much for looking into this issue!

Here is the warning message which I get when I run the query:

Warning message:
In result_create(conn@ptr, statement, immediate) :
  Closing open result set, cancelling previous query

and when I run

> head(experiment)
  study_accession experiment_accession                                                 name
1            SDY1             EXP14861 Response to Free IgE, IgG-a Amb a, and IgG-a Ragweed
2            SDY1             EXP14862                                IgM a Amb a1 response
3            SDY1             EXP14863                                 IgE-a Amb a response
4            SDY1             EXP14864                               IgE-a Ragweed response
5            SDY1             EXP14865                               IgG-a Ragweed response
6            SDY1             EXP14866         Response to IgG1-a Amb a1, and IgG4-a Amb a1
  measurement_technique result_schema result_table number_expsamples    tree_id
1                 ELISA         ELISA elisa_result              1768 experiment
2                 ELISA         ELISA elisa_result               250 experiment
3                 ELISA         ELISA elisa_result              1219 experiment
4                 ELISA         ELISA elisa_result               608 experiment
5                 ELISA         ELISA elisa_result               249 experiment
6                 ELISA         ELISA elisa_result               282 experiment

[vnijs]

OK. Looks like the data.frame was created. If you run the below after the DB queary, does the data.frame show up in the dataset dropdowns?

register("experiment")

[ypkoshka]

Yes, it does: (click to expand)