Baseline to stem_table

The baseline table contains one row per person and a column for each field. There can be thousands of columns and each column name is structured as field_id-instance.array (e.g. 20001-1.15). The field_id encodes the variable, the instance indicates one of the four assessment centre visits (ranging from 0 to 3) and with the array index multiple values can be given for the same field_id at the same visit.

To map all these columns to individual events, we map them first to a staging table: the stem table. Depending on the domain of the target concepts, the events are then mapped to their respective tables. See the section on stem table mapping

The input for this transformation are mapping tables of thefield_id . Each field can have multiple values, for which a separate mapping is made. The mappings are made using a modified version of Usagi, allowing for mapping of values and having different target types. They are saved as default Usagi save files in this location: resources/baseline_field_mapping. This folder also contains a readme with a detailed description of how the mappings were made.

Each field or field/value combination can have a mapping to an event, unit and/or value concept. Also, each field is associated to a date field_id (resources/baseline_field_mapping/date_field_lookup.csv). Based the mappings given, both the semantic mapping and structural mapping is made. The value can be either a discrete (mapping to a value_as_concept_id), numeric (mapping to value_as_number) or a text (mapping to value_as_string).

• discrete if the value is coded
• numeric if the value can be converted to float (or if the value is missing)
• text in all other cases

The ETL is implemented as follows:

• Loop through all rows of the baseline table:
  • Loop through all columns in the row (except for the eid):
    1. If the value is empty, go to next column.
    2. From the column name, extract field_id and instance index (we ignore the array index)
    3. Look up target concepts by field_id and value. If the field_id is ignored, we continue to the next field.
    4. Look up date_field_id by field_id. Create the column name, using the extracted instance and array ‘0’. (e.g. 53-1.0, if the date_field_id is ‘53’ and the instance is ‘1’)
    5. Map record to stem table columns according to the overview below.

Notes:

• The field is considered numeric if it has no value mappings
• For numeric fields, values of -1 and -3 are filtered out.
• A field can also be ‘ignored’ meaning that no OMOP record should be created for this field.
• Fields not given in the mapping tables are also included with a concept_id of 0. The source_concept_id is looked up for the given field_id.
• Mappings that have not been approved will be mapped to a 0 (see the mapping status column in the mapping tables).
• If a field has no value mappings, but the value can’t be converted to a float, then it is treated as free-text and populates the value_as_string field.
• There are some cases where instances run higher than 3. These correspond to positions in the death or cancer registry and are handled separately.
• Waist and Hip circumference (fields 48 and 49) are mapped to observation concepts (4172830 4111665, respectively). There does not exist an equivalent target concept for both in the measurement domain (see also issue #176).

There are 26 more columns in the stem table, each corresponding to columns in the event tables. These are not used and omitted for brevity.

Example

Given te following baseline row:

Loop through columns:

• Columns with field_id 31 (gender) and 53 (visit date) are marked as ‘IGNORED’ in the mapping files. We skip these for the mapping to stem table. These fields are used for other mappings (gender in the mapping to person).
• Column 46-0.0 (“Hand grip strength, left”, initial visit)
  1. Not empty
  2. field_id=46, instance=0, (array=0)
  3. Lookup mapping for field 46 in mapping tables, we get:

     event_concept_id=44805437
     value_as_number=12.5
     unit_concept_id=9529
     value_as_concept_id=None
     source_value=46
     

  4. Lookup the date field associated with this field_id. We get: 53. Together with the instance (0), this means we have to retrieve the date from column 53-0.0 (=2010-01-01).
  5. Map (see result for ‘Record 1’ in table below)
• Column 2443-1.0 (“Diabetes diagnosed by doctor”, repeat visit)
  1. Not empty
  2. field_id=2443, instance=1, (array=0)
  3. Lookup mapping for field 2443 in mapping tables, we get:

     event_concept_id=4188893
     value_as_number=None
     unit_concept_id=None
     value_as_concept_id=201820
     source_value=2443|1
     

  4. Lookup the date field associated with this field_id. We get: 53. Together with the instance (1), this means we have to retrieve the date from column 53-1.0 (=2020-06-06).
  5. Map (see result for ‘Record 2’ in table below)

---