Introduction

In R, data are often stored in data frames which are tables in which each row represents a record and each column a variable. Because data frames are highly used, they have been improved in different objects such as tibble, data.table or AnnotatedDataFrame.

However, in many projects, the data do not fit in only one table but they are organized in different data frames, each of them dealing with a specific concept. These tables are often related to each other by some variables. Depending on the diversity of the concepts, it can be difficult to remember what these tables represent and how they are connected. Fortunately, because they are tables, a set of data frames can be directly documented using a relational data model.

The datamodelr R package provides tools to document relational data. The generate data models are leveraged by the dm R package to interact more easily with relational data.

Here we present the ReDaMoR package which also allows the manipulation of relational data models in R but with an approach quite different from the one implemented in datamodelr. It provides functions to create, import and save relational data models. These functions are accessible through a graphical user interface made with Shiny.

The main features of ReDaMoR are the following:

• Create data model from scratch with a graphical user interface
• Save data model in a json format
• Import SQL data model generated with MySQL Workbench
• Document data types as R types (character, numeric, logical, …)
• Add comments to tables and fields to improve model documentation
• Check and auto-correct model constraints (types, keys, uniqueness, mandatory fields)
• Confront data to the model to check their compatibility

The ReDaMoR R package is licensed under GPL-3 and its source code is available on github.

Installation

install.packages("ReDaMoR")

devtools::install_github("patzaw/ReDaMoR")

Creating and modifying relational data using the graphical user interface

library(ReDaMoR)
m <- model_relational_data()

m <- model_relational_data(recover_RelDataModel())

hpo_model <- read_json_data_model(
  system.file("examples/HPO-model.json", package="ReDaMoR")
)
plot(hpo_model)

## Edit the model
# m <- model_relational_data(hpo_model)

library(ReDaMoR)
model_relational_data()

Confronting data

Data can be confronted to the model using the confront_data() function. During this process the following checks are performed:

• All the tables in the model and only those are available.
• For each table:
  • All the columns in the model and only those are available.
  • The type of each column corresponds to the type in the model.
  • Not any NA values are found in non-nullable columns.
  • There is no duplicated value in unique columns.
  • There is no duplicated value in unique indexes.
• Foreign keys are tested according to their cardinality: all values in on table should be available in the other table if the corresponding minimum cardinality is greater than 0.

A subset (500 phenotypes among more than 14 000 in the original resource) of the HPO⁽¹⁾ is provided within the package and can be directly confronted to the data model. The function prints a report with messages about the global success of the data model confrontation and additional failure or warning messages for relevant tables.

confrontation_report <- confront_data(
  hpo_model,
  path=list.files(
    system.file("examples/HPO-subset", package="ReDaMoR"),
    full.names=TRUE
  ),
  returnData=TRUE
)
#> Processing "HPO_hp" (table 1 / 9)
#> Processing "HPO_altId" (table 2 / 9)
#> Processing "HPO_sourceFiles" (table 3 / 9)
#> Processing "HPO_diseases" (table 4 / 9)
#> Processing "HPO_diseaseHP" (table 5 / 9)
#> Processing "HPO_diseaseSynonyms" (table 6 / 9)
#> Processing "HPO_parents" (table 7 / 9)
#> Processing "HPO_descendants" (table 8 / 9)
#> Processing "HPO_synonyms" (table 9 / 9)
#> Model
#> SUCCESS
#> 
#> Check configuration
#>    - Optional checks: unique, not nullable, foreign keys
#>    - Maximum number of records: Inf
#> 
#> HPO_hp
#> SUCCESS
#> Field issues or warnings
#>    - description: SUCCESS Missing values 117/500 = 23%

This report can also be formatted using markdown and included in a document such as this one.

# view_confrontation_report(confrontation_report) # Use RStudio viewer
format_confrontation_report_md(
  confrontation_report,
  title="Example: Confrontation with original data",
  level=1, numbered=FALSE
) %>%
  cat()

The returnData argument is used to keep the data in the confrontation report.

hpo_tables <- confrontation_report$data

In the example below the data are altered and confronted again to the data model. The confrontation report shows the discrepancies between the model and the data in order to facilitate the correction of one or the other.

hpo_tables$HPO_diseases <- hpo_tables$HPO_diseases %>% slice(1:100)
hpo_tables$HPO_synonyms[1:10, "synonym"] <- NA
hpo_tables$HPO_hp <- hpo_tables$HPO_hp %>% mutate(level=as.character(level))
confront_data(hpo_model, hpo_tables, verbose=FALSE) %>%
  format_confrontation_report_md(
    title="Example: Confrontation with altered data",
    level=1, numbered=FALSE
  ) %>%
    cat()

hpo_tables <- confrontation_report$data
new_model <- df_to_model(
  list=names(hpo_tables), envir=as.environment(hpo_tables)
)
new_model %>%
  auto_layout(lengthMultiplier=250) %>%
  plot()

# model_relational_data(new_model)

Support of matrices

Matrices (2 dimensions) are supported by ReDaMoR (>= 0.5). A matrix is modeled as a table with 3 and only 3 fields: 2 of types row and column and the third of your choice. The row and column compose the primary index of this special table, but they cannot be unique independently (a cell in a matrix is always identified by its row and its column). The example below shows a model of a gene_expression matrix: the rows correspond to genes, the column to samples and the value to the gene expression value in each sample. Genes and samples are further described in the genes and samples tables.

ge_model <- read_json_data_model(
  system.file("examples/GE-model.json", package="ReDaMoR")
)
plot(ge_model)

TKCat: Tailored Knowledge Catalog

The aim of TKCat (Tailored Knowledge Catalog) is to facilitate the management of data from knowledge resources that are frequently used alone or together in research environments. In TKCat, knowledge resources are manipulated as modeled database (MDB) objects. These objects provide access to the data tables along with a general description of the resource and a detail data model generated with ReDaMoR documenting the tables, their fields and their relationships. These MDB are then gathered in catalogs that can be easily explored an shared. TKCat provides tools to easily subset, filter and combine MDBs and create new catalogs suited for specific needs.

A modeled database (MDB) in TKCat gathers the following information:

• General database information including a mandatory name and optionally the following fields: title, description, url, version and maintainer.
• A data model created using the [ReDaMoR][redamor] package.
• A list of tables corresponding to reference concepts shared by different MDBs. The way these concepts are identified is defined in specific documents called collections.
• The data themselves organized according to the data model.

MDB objects can be subsetted, combined, filtered, joined and merged. TKCat supports different implementations of MDB objects such as memoMDB (data in memory), fileMDB (data in files) and chMDB (data in ClickHouse database). More information is provided in the TKCat user guide.

Acknowledgments

This work was entirely supported by UCB Pharma. (Early Solutions department)

References

(1) Sebastian Köhler, Leigh Carmody, Nicole Vasilevsky, Julius O B Jacobsen, et al. Expansion of the Human Phenotype Ontology (HPO) knowledge base and resources. Nucleic Acids Research. (2018) doi: 10.1093/nar/gky1105