Guide for Ingesting a Simple Four Table Dataset

Overview

This walkthrough describes phenotypic data ingestion using a data dictionary file, a codings file if needed, an optional entity dictionary file, and accompanying data CSV files. The files are loaded using the Data Model Loaderapp, which validates and ingests the input CSV files to create an Apollo dataset. This Dataset is then accessible using the Cohort Browser, or using JupyterLab and our Python SDK, dxdata.

The following steps show how to organize your data into the required file sets following the Data Ingestion Key Steps. These files can then be loaded using the Data Model Loaderapp to create a database encapsulated by a Dataset record, which are then immediately accessible for use.

Requirements

• DNAnexus Apollo License

• Access to the Data Model Loader app

• A way to manipulate and create CSV files

• Uploader or greater permissions in the project the app will be running

Example Raw Files

Guide

Step 1. Identify Your Data

Specify the Data to Be Used

For this dataset the following CSV files from the Example section above will be used: patient.csv, hospital.csv, encounter.csv, and test.csv. The data has a structure as follows:

• Many patients will point to one hospital but some may be missing hospital information.

• A patient may have none, one, or many encounters.

• Each encounter may have none, one, or many tests.

Specify the Main Entity and Main Field

For this dataset create cohorts of different patients to perform analysis on. Some examples of cohorts that can be built are:

• The Patients that were in Hospital X and had an Encounter with Doctor Y

• The Patients with a weight over A

• The Patients born before a date, with a risk factor of H that had an Encounter with Doctor Y

Given these types of questions and the data structure, the main entity will be the patient. The patient data includes a patient_id that is unique for the table and will be used as the key (main field).

Specify Additional Entities

Since the data already has a one-to-many relationships, there are no one-to-one relationships, and there are no large sets of related multi-select fields, the secondary entities will be kept as:

• Hospital

• Encounter

• Test

Step 2. Provide File Specifications

While the data ingestion key steps document guides the user through column by column for the different data files, this walkthrough will go through one entity at a time. This is recommended to ensure precision when working with a larger or multi-entity dataset.

The Patient CSV (main entity) file should be formatted as follows:

Create a Data Dictionary File

Create a data_dictionary.csv file and fill in the fields for entity and name. Add global as the primary_key_type value for patient_id.

The Data Dictionary CSV file should be formatted as follows:

Specify Field Characteristics

For each field in each entity, indicate is_sparse_coding and is_multi_select, and specify the field's type.

Layer in the types for the different fields. For this example, there are the following groupings:

• Integer: patient_id, age, hospital_id

• Date: date_of_birth

• Float: weight

• String: name, risk, resident_state

  • Multi-select: title

The Data Dictionary CSV file should be formatted as follows:

Specify Coding Settings for Categorical Fields

For categorical fields, build the codings.csv file and determine coding_name. Layer in the coding for categorical fields and fields that you want to summarize using categorical chart types. For patient, create categorical selections for risk, resident_state_prov, and titles. For these, first generate a codings file from the dictionary as follows: the raw code in the data is populated in the code column, the text to display in the Cohort Browser is in the meaning column. If it is a hierarchical field such as state_prov, the parent_code points to the code of the parent for the entry. The display_order can be used to override the order codes are displayed in summary tiles. The coding file should look as follows:

The Coding CSV file should be formatted as follows:

Now that you created the coding file, go back and fill in the coding_name column in data_dictionary.csv with the corresponding coding_name from the coding.csv.

The Data Dictionary CSV should be formatted as follows:

Specify Data Formats

Now you can validate that your fields will be ingested as their expected ingestion data type. The following are the data type to field mappings expected for this walkthrough:

• Integer: Patient ID, Age, Hospital ID

• String: Name

  • String Categorical: Risk

  • String Categorical Hierarchical: Resident State Province

  • String Categorical Multi-select: Title

• Date: Date of Birth

• Float: Weight

Define Entity Relationships

Now, link the entities together by defining their relationships. For the Patient entity, use one-to-one or many-to-one relationships as appropriate. To create these links, enter the referenced entity and field in the format <entity>:<field name> in the referenced_entity_field column. Specify the type of relationship in the relationship column. If a join cannot be made for all rows, the system will automatically handle missing cases.

For this example dataset, create the following relationships:

• Link hospital to patient using hospital_id with a many_to_one relationship.

• Link encounter to patient using patient_id with a many_to_one relationship.

• Link test to encounter using encounter_id with a many_to_one relationship.

The Data Dictionary CSV file should be formatted as follows:

Add Additional Descriptive Information

Now that you have the core data relationships and types in place, it's time to add metadata about the field (title, description, units, and linkout) along with specifying the folder path desired to be shown in the field selector of the Cohort Browser. For more information on each field, see data dictionary details.

Instead of following the examples generally set for entities for folder paths, put the identifiers in their own "ID" folder and patient identifying information (PII) in a subfolder under "Patient" called "PII". Foreign keys are hidden from the field selector.

The Data Dictionary CSV file should be formatted as follows:

Now that the patient is set, repeat the steps for the other entities: hospital, encounter, and test. Feel free to use this coding as a guide for categorical values.

Generate Entity Details

After that, the only step remaining is to generate the entity_metadata.csv to clean up the labels that the browser will use for the entities (refer to the detailed docs for more guidance). Blank spaces are left in the label fields if the field is a repeat of the entity_title.

The Entity Metadata CSV should be formatted as follows:

Step 3. Ingest Data

Examples of Completed Files Ready for Ingestion

You now have a raw CSV file above, a data dictionary file, an entity metadata file, and a codings file. If you struggled, below are exemplar files that can be used for ingestion:

Running the Data Model Loader

With the files provided, configure the Data Model Loaderand run the ingestion. See below for an example configuration.

The four raw example files are input into the Data CSVs input field.

Step 4. Explore and Analyze Data

Once the Data Model Loader job has completed, you will see a dataset and a database output. If you used the supplied metadata files, you should see something similar to this in the Cohort Browser: