Column Transformations

Overview

Once you have created a syntactic mapping, you can apply one or more transformations to it.

Transformations are essential for cleaning, organizing, and standardizing source data so that its information is preserved and accurately represented in the target vocabulary or coding system.

For example, imagine that you have a source field called ethnic_background and you need to map it to a target field named race_concept_id. You could use the "Custom Mapping" type of transformation to do this. Here is an example of what that could look like:

Source (ethnic_background)

Target (OMOP)(race_concept_id)

8527

4212311

8515

38003598

38003579

When the transformation is run, the value in the source is mapped to the corresponding value in the target. Using the table above, a value of "1" would be transformed to "8527."

Transformation Fields

A transformation consists of the following fields.

Note that not all transformations will require all fields to be populated.

Transformation Type: This specifies the kind of transformation you're using. See here for more detail.
Transformation Input(s) and Output: Lists the names of source fields and their corresponding target output.
Description: Offers a concise explanation of the transformation type.
Error Message: Shows any error messages related to the transformation.
Transformation Configuration: Displays any configuration/code for the transformation.
Error Handling: Specifies actions to take if there's a failure, like a missing source value or code error, and sets a default value in such cases.

Editing a Transformation

Prerequisites

You must have the "Manage Data Mappings" permission.
A syntactic mapping should have already been created.

Steps

Select the syntactic mapping

In the Syntactic Mappings page, select the mapping that you want to edit transformations for.

Open the transformation editor

Find the mapping entry you want to edit, then click the pencil icon in the Transformation column. The Transformations Panel will appear.

Add / Edit a transformation

Click the dropdown menu to select the desired transformation type and edit the transformation as desired.

Note the Transformation Input (source) and Transformation Output (target). It should reflect what you already chose in the syntactic mapping entry.

Reorder Transformations

Click the transformation's handle (six domino-looking dots), and drag the transformation in the order you want it to run. Do this for each transformation that you want to reorder.

When code runs, configured transformations are executed one after another for a given syntactic mapping entry.

Save the changes

When complete, click the "Save" button.

Values that cannot be converted are left empty in the output. See the code run logs for a breakdown of conversion failures.

Transformation Types

Transformation Type

Description

Custom Mapping

Maps values from the source dataset to corresponding values in the target dataset

e.g., mapping a number to the day of the week

Semantic Mapping

Applies a semantic mapping to transform values

e.g., mapping an input text to an OMOP concept ID

Set Value

Assigns a specific value to all rows in the field

e.g., setting all values to the number 1

Convert Date

Changes the date to a different format

e.g., convert a date from MM/DD/YY format to YYYY-MM-DD.

Stable UUID

Generates an encrypted unique identifier based on the input. The same input will always generate the same unique identifier.

Row Level Code

Applies Python code to each row of input in order to generate the output field value. The input row is available in the "row" dictionary, and the code is expected to generate an "output" variable with the output.

e.g., this can be used to transform the output of a specific source field to uppercase characters

Table Level Code

Applies Python code to generate an entire column with the output field values. Has access to all input tables and fields, and can utilize Python code to generate the output column.

VLookup

Looks up each source value in the lookup table, matches a row in the lookup table based on the lookup key field, and returns the value from the matching row in the lookup value field.

Transformation Type: Custom Mapping

A Custom Mapping transformation looks up each source value (input field value) in a custom mapping dictionary to find the target value (output field value). This custom mapping dictionary is defined in the transformation configuration, and makes it easy to create quick lookup tables for fixed value transformations.

Follow the steps here to access the transformation panel.

Select the Custom Mapping transformation.

Add the custom mapping to the code section. We currently support CSV or JSON.

CSV - the syntax is: input value,output value. Place each set of values on a separate line. For example:
```
m,Male
f,Female
o,Other
```

JSON - should be a valid JSON dictionary. For example:

{
  "m": "Male",
  "f": "Female",
  "o": "Other"
}

Indicate what happens if the mapping from the source value is not found.

You can choose to:

Skip the row - this entire row will be omitted from the output table.
Fail - the entire code run is failed.
Use the source value - the output value will be set to the source value without any changes
Set to default - set the output to a default value (e.g. "" or "0" or "MISSING"). If you select "Set to default", another input appears for you to fill in the desired default value.

When complete, click the "Save" button.

Transformation Type: Semantic Mapping

A Semantic Mapping transformation applies a semantic mapping to the input field value to set the output field value. This means that each value in the input field is looked up in the semantic mapping, and the matching output value is set in the output field.

Follow the steps here to access the transformation panel.

Select the Semantic Mapping transformation.

Select the target vocabulary of the semantic mapping that you'd like to use in this transformation: OMOP V5.4 or Custom Vocabulary.

For OMOP - Select the Target Domain.
For Custom Vocabularies, select the specific Custom Vocabulary to use

Indicate the transformation output type, which means the type of output to use from the matched term.

The output type can be:

Concept ID or Code - use the matched term's concept ID or code (e.g. "35816080") as the output. For custom vocabularies with no distinction between the term display name and code, the code will be identical to the display name.
Concept Name or Display Name - use the matched term's concept name or display name (e.g. "Aspirin") as the output.

Indicate what should happen if the source value matches an entry in the semantic mapping that has not yet been approved in FCP (more information on how to approve semantic mapping entries is here).

You can choose to:

Use the top recommendation from the semantic mapping recommendations generated by FCP
Treat such values as missing from the mapping.

Indicate what should happen if the source value is missing from the semantic mapping.

You can choose to:

Skip the row - this entire row will be omitted from the output table
Fail - the entire code run is failed
Use the source value - the output value will be set to the source value without any changes
Set to default - set the output to a default value (e.g. "" or "0" or "MISSING"). If you select "Set to default", another input appears for you to fill in the desired default value.

When complete, click the "Save" button.

Transformation Type: Set Value

A Set Value transformation sets a specific value in all rows of the output field.

Follow the steps here to access the transformation panel.

Select the Set Value transformation.

Enter the transformation output value as the value that you would like to be set in the output field for ALL output rows. It should be a number or string.

When complete, click the "Save" button.

Transformation Type: Convert Date

A Convert Date transformation converts an input value that is a date in one format into an output date in a different format.

For Example:

Input

Input Format

Output Format

Output

01/15/2023

MM/DD/YYYY

YYYY-MM-DD

2023-01-15

15-Jan-23

DD-MMM-YY

YYYY-MM-DD

2023-01-15

The same can be achieved using a Row Level Code transformation, but this transformation makes it easier to avoid common pitfalls in python date transformation code.

Follow the steps here to access the transformation panel.

Select the Convert Date transformation.

Enter the Input and Output date formats.

These should both be in the python arrow library format (see supported tokens). For example, if your input is something like "Dec 31, 2024" and you'd like to transform it to something like "2024-12-31", you could use the input format "MMM D, YYYY" and the output format "YYYY-MM-DD".

When complete, click the "Save" button.

Transformation Type: Stable UUID

A Stable UUID transformation returns a unique output value for each input value by encrypting the input value. The encryption is "stable" in the sense that for the same input value, the same output value will always be generated.

Follow the steps here to access the transformation panel.

Select the Stable UUID transformation.

There is no additional configuration required for the Stable UUID transformation.

When complete, click the "Save" button.

Transformation Type: Row Level Code

A Row Level Code transformation applies python code to each row of data to transform the input values into the output that will be placed in the output field for that row.

Follow the steps here to access the transformation panel.

Select the Row Level Code transformation.

Enter a Python code snippet that you want to apply to each row.

The code snippet has access to the input fields in a special "row" variable. This is a dictionary with each field name mapped to the field value. For example, if there is a "gender" source field, your code can access its value for the input row via:
```
row["gender"]
```
The code snippet should set the desired output value to a special "output" variable. For example, if you'd like to take the input value of the "gender" field, and set the output to be the first letter of the input value in uppercase, your code could look like this:
```
output = row["gender"][:1].upper()
```

Indicate what happens if the code fails (e.g. there is an exception when executing the code).

You have three options:

Skip row - this entire row is omitted from the output.
Fail - the Data Harmonization code run fails - you will see the error message in the detailed log of the code run.
Set to default - set a specific value (e.g. "" or "ERROR") to the output value in rows in which the error occurred. If you select to set to a default value, a new input appears that allows you to enter the default value. The value can be a string or a number.

When complete, click the "Save" button.

Transformation Type: Table Level Code

A Table Level Code transformation applies python code to process input field values and generate an entire column of output values.

Follow the steps here to access the transformation panel.

Select the Table Level Code transformation.

Enter a Python code snippet that you want to run in order to generate the entire column of output values.

The code snippet has access to all fields in all input data sources.
- Each input data source is available in a variable with a name matching the schema name in snake case. For example, if the source data schema is called "Patient Table", the matching variable will be "patient_table".
- Each data source variable is a dictionary with each field name mapped to the field value. For example, if there is a "gender" field in the "Patient Table" dataset, your code can access its values via:
  patient_table["gender"]
The code snippet can use methods from pandas (imported as pd), numpy (imported as np), and arrow.
The code snippet should set the desired output values to a special "output" variable. The output should be a one-dimensional Pandas Series or array, and its length should be the number of rows in the output table. The values in the output will be set directly as the column values in the output table in the order provided by the code snippet. For example, if you would like to set the output value to be the element-wise maximum between two input fields (assuming that they are of the same length, matching order, and you'd like the output to be of the same length and in the same order), you could use something like the following:
```
date_of_service = pd.to_datetime(encounters["DateOfService"], format="%Y-%m-%d")
procedure_end_date = pd.to_datetime(procedures["ProcedureEndDate"], format="%Y-%m-%d")
output = np.maximum(date_of_service, procedure_end_date)
```

Indicate what happens if the code fails

You have two options: fail the entire code run or set the output to a default value.

The default value can be a string or a number and it will be set in the output field for all rows.

When complete, click the "Save" button.

Transformation Type: VLookup

A VLookup transformation performs a Vertical Lookup. This means that for each value in the source field, it looks up a matching value in the lookup table within the lookup key field. If a match is found, the output is set to the value of the lookup value field in the matching row. This is similar to the VLookup functionality in spreadsheets.

Follow the steps here to access the transformation panel.

Select the VLookup transformation.

Fill in the Lookup Table - this is a source data schema in which you want to match values from the source field.

Fill in the Lookup Key Field - this is the field in the Lookup Table that should have values that match the values in the source field.

Fill in the Lookup Value Field - this is the field in the Lookup Table from which the output will be filled in, if a matching row is found.

Indicate what should happen if no match is found when looking up a specific source field value in the lookup key field.

There are several options available: skip the row, cause the code run to fail, use the source value or set the output to a default value (return a number of string).

Indicate what should happen if multiple matches are found when looking up a specific source field value in the lookup key field.

There are several options available: use the first match, use the last match, fail the code run, or set to a default value (return a number or string).

When complete, click the "Save" button.

Note that source field values don't need to be unique (the same value can appear multiple times in the source field and will be transformed to the same value in the output consistently).

PreviousSyntactic Mapping

NextCustom Data Models

Last updated 1 month ago

Was this helpful?