> For the complete documentation index, see [llms.txt](https://docs.rhinofcp.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.rhinofcp.com/data-harmonization/creating-and-editing-semantic-and-syntactic-transformations.md).

# Creating and Editing Semantic and Syntactic Transformations

## Creating and Editing a Transformation Overview

Once you have [created a syntactic mapping](/data-harmonization/creating-editing-and-removing-a-syntactic-mapping.md), 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 field called `ethnic_background` in your source dataset 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) |
| --------------------------- | -------------------------------- |
| 1                           | 8527                             |
| 2                           | 4212311                          |
| 3                           | 8515                             |
| 4                           | 38003598                         |
| 5                           | 38003579                         |

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

### Parts of a Transformation

Creating transformations is straightforward with Rhino FCP. Here is an illustration of the parts of a transformation. Note that not all transformations will have all of these parts.

![](/files/23cb8c56e5871cfe4db42d838c67167b0f0e7e78)

* **Transformation Type**: This specifies the kind of transformation you're using. Transformation types include: Convert Date, Custom Mapping, Semantic Mapping, Set Value, Stable UUID, Row Level Code, Table Level Code, and VLookup.
* **Transformation Inputs and Outputs**: Lists the names of source fields and their corresponding target outputs.
* **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.

### Transformation Types and Descriptions

The following table lists the transformation types and descriptions.

| Transformation Type                                                                                                                                           | Description                                                                                                                                                                                                                                                                                                                    |
| ------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| [Custom Mapping](/data-harmonization/creating-and-editing-semantic-and-syntactic-transformations.md#creating-and-editing-a-custom-mapping-transformation)     | Maps values from the source to corresponding values in the target dataset, like mapping a number to the day of the week.                                                                                                                                                                                                       |
| [Semantic Mapping](/data-harmonization/creating-and-editing-semantic-and-syntactic-transformations.md#creating-and-editing-a-semantic-mapping-transformation) | Applies a semantic mapping to transform values, like mapping an input text to an OMOP concept ID.                                                                                                                                                                                                                              |
| [Set Value](/data-harmonization/creating-and-editing-semantic-and-syntactic-transformations.md#creating-and-editing-a-set-value-transformation)               | Assigns a specific value to all rows in the field, like setting all values to the number 1.                                                                                                                                                                                                                                    |
| [Convert Date](/data-harmonization/creating-and-editing-semantic-and-syntactic-transformations.md#creating-and-editing-a-convert-date-transformation)         | Changes the date to a different format. For example, you can use this to convert a date from MM/DD/YY format to YYYY-MM-DD.                                                                                                                                                                                                    |
| [Stable UUID](/data-harmonization/creating-and-editing-semantic-and-syntactic-transformations.md#creating-and-editing-a-stable-uuid-transformation)           | Generates an encrypted unique identifier based on the input. The same input will always generate the same unique identifier.                                                                                                                                                                                                   |
| [Row Level Code](/data-harmonization/creating-and-editing-semantic-and-syntactic-transformations.md#creating-and-editing-a-row-level-code-transformation)     | 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. For example, this can be used to transform the output of a specific source field to uppercase characters. |
| [Table Level Code](/data-harmonization/creating-and-editing-semantic-and-syntactic-transformations.md#creating-and-editing-a-table-level-code-transformation) | 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](/data-harmonization/creating-and-editing-semantic-and-syntactic-transformations.md#creating-and-editing-a-vlookup-transformation)                   | 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.                                                                                                                                        |

### Ordering Transformations

When code runs, configured transformations are executed one after another. If you want to change the order of transformation execution for specific syntactic mapping entries, complete the following steps.

{% stepper %}
{% step %}

#### Choose the mapping to modify

In the Syntactic Mapping page, choose the mapping you want to modify.
{% endstep %}

{% step %}

#### Open the Transformations Panel

Select the Edit Pencil in the Transformation field for the entry you want to edit. The Transformations Panel will appear.

![](/files/5acfbcf430cb2bcc8085e5379fad0879255beee7)
{% endstep %}

{% step %}

#### Reorder the 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.

![](/files/4346367a1a0517b37e83bf3995175378bbbcd390)
{% endstep %}

{% step %}

#### Save your changes

When done, click Save at the bottom of the page.
{% endstep %}
{% endstepper %}

### Editing a Transformation

To edit a transformation, complete the following steps.

{% stepper %}
{% step %}

#### Select the mapping

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

{% step %}

#### 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.
{% endstep %}

{% step %}

#### Edit the transformation

Edit the transformation as desired.
{% endstep %}

{% step %}

#### Save the changes

When complete, click the Save button.
{% endstep %}
{% endstepper %}

## Creating and Editing a Set Value Transformation

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

### Prerequisites

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

### Creating a Set Value Transformation

To create a Set Value transformation, complete the following steps.

{% stepper %}
{% step %}

#### Open the Transformations Panel

1. In the Syntactic Mappings page, choose the mapping you want to edit.
2. Click the Edit Pencil in the Transformation field for the entry you would like to edit transformations for.\
   ![](/files/c945dfc263f9c305209733030073509a9757c3c4)
3. The Transformations Panel will appear. Click the Add Transformation button.\
   ![](/files/71671f4b0e44608c557d5e91cbdc135bd4714803)
4. Click the dropdown menu to select the desired transformation type.

   ![](/files/e82d89b35e2767b16332c38538dc1fe66d3dcb41)
5. Select the Set Value transformation

   ![](/files/d14f32fc0e1d338c118930317f4673002fb99b25)
   {% endstep %}

{% step %}

#### Fill in the Transformation Configuration

1. Note the Source Input and Target Output. It should reflect what you already chose in the syntactic mapping entry.
2. 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.
3. When complete, click the Save button.
   {% endstep %}
   {% endstepper %}

## Creating and Editing a Convert Date Transformation

A convert date transformation converts an input value that is a date in one format into an output date in a different format. 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.

### Prerequisites

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

### Creating a Convert Date Transformation

To create a Convert Date transformation, complete the following steps.

{% stepper %}
{% step %}

#### Open the Transformations Panel

1. In the Syntactic Mappings page, choose the mapping you want to edit.
2. Click the Edit Pencil in the Transformation field for the entry you would like to edit transformations for.\
   ![](/files/12a8e6b5531fa1106f204de7d984a523ff916b55)
3. The Transformations Panel will appear. Click the Add Transformation button.\
   ![](/files/631f39fdee91e4206b50c8afbdbade75bdd413b5)
4. Click the dropdown menu to select the desired transformation type.

![](/files/7872a5a736a5c5e809ebf8147f0789066fce0964)

5. Select the Convert Date transformation

![](/files/5e8e54ec566c20316362615fbb2f08c549923476)
{% endstep %}

{% step %}

#### Fill in the Transformation Configuration

1. Note the Source Input and Target Output. It should reflect what you already chose in the syntactic mapping entry.
2. Enter the Input and Output date formats. These should both be in the python arrow library format ([see supported tokens](https://arrow.readthedocs.io/en/latest/guide.html#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".
3. When complete, click the Save button.
   {% endstep %}
   {% endstepper %}

## Creating and Editing a Custom Mapping Transformation

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.

### Prerequisites

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

### Creating a Custom Mapping Transformation

To create a Custom Mapping transformation, complete the following steps.

#### Step 1: Open the Transformations Panel

{% stepper %}
{% step %}
In the Syntactic Mappings page, choose the mapping you want to edit.
{% endstep %}

{% step %}
Click the Edit Pencil in the Transformation field for the entry you would like to edit transformations for.

![](/files/c2f16ba79bf515ac242700d9ce75a8a80f104805)
{% endstep %}

{% step %}
The Transformations Panel will appear. Click the Add Transformation button.

![](/files/407db1252a93360350b572c1bfd1953de13aa037)
{% endstep %}

{% step %}
Click the dropdown menu to select the desired transformation type.

![](/files/a8ed2111810cc3ffcf58f760a71436160f5e11ef)
{% endstep %}

{% step %}
Select the Custom Mapping transformation.

![](/files/44bcee96e90170321280f908230fb108c9f601ff)
{% endstep %}
{% endstepper %}

#### Step 2: Fill in the Transformation Configuration

{% stepper %}
{% step %}
Note the Source Input and Target Output. It should reflect what you already chose in the syntactic mapping entry.
{% endstep %}

{% step %}
Add the custom mapping to the code section. The custom mapping can use one of two formats: 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"
  }
  ```

{% endstep %}

{% step %}
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.
  {% endstep %}

{% step %}
When complete, click the Save button.
{% endstep %}
{% endstepper %}

## Creating and Editing a Stable UUID Transformation

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.

### Prerequisites

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

### Creating a Stable UUID Transformation

To create this transformation, complete the following steps.

{% stepper %}
{% step %}

#### Open the Transformations Panel

1. In the Syntactic Mappings page, choose the mapping you want to edit.
2. Click the Edit Pencil in the Transformation field for the entry you would like to edit transformations for.\
   ![](/files/0506762d95d25cff9599f646cfb882c12ea42582)
3. The Transformations Panel will appear. Click the Add Transformation button.\
   ![](/files/9f6a5b8247455391cc1fe291bf33b0ec2c9ee615)
4. Click the dropdown menu to select the desired transformation type.

   ![](/files/dc5bd61b31c116acc0a3216ba3ac4dd4d4a322e9)
5. Select the Stable UUID transformation.

   ![](/files/4ad45e6bededd052f6f75a842bfc7342029579e4)
   {% endstep %}

{% step %}

#### Save the Transformation Configuration

1. Note the Source Input and Target Output. It should reflect what you already chose in the syntactic mapping entry.
2. There is no additional configuration required for the Stable UUID transformation.
3. When complete, click the Save button.
   {% endstep %}
   {% endstepper %}

## Creating and Editing a Semantic Mapping Transformation

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.

### Prerequisites

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

### Creating a Semantic Mapping Transformation

{% stepper %}
{% step %}

#### Open the Transformations Panel

1. In the Syntactic Mappings page, choose the mapping you want to edit.
2. Click the Edit Pencil in the Transformation field for the entry you would like to edit transformations for.\
   ![](/files/cb952a9b1610def65ec30a9a060912e35583514f)
3. The Transformations Panel will appear. Click the Add Transformation button.\
   ![](/files/a93d5b4772a2fc9115d525221471954123ed3ca2)
4. Click the dropdown menu to select the desired transformation type.

   ![](/files/f8266c7e7cda44a92eda3013a09e7830227321c8)
5. Select the Semantic Mapping transformation.

   ![](/files/d862de1fabacdafa8241b3763d7382ef8342505b)
   {% endstep %}

{% step %}

#### Fill in the Transformation Configuration

1. Note the Source Input and Target Output. It should reflect what you already chose in the syntactic mapping entry.
2. 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
3. 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.
4. 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](/data-harmonization/creating-reviewing-editing-and-removing-a-semantic-mapping.md)). You can choose to:
   * Use the top recommendation from the semantic mapping recommendations generated by FCP
   * Treat such values as missing from the mapping.
5. 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.
6. When complete, click the Save button.
   {% endstep %}
   {% endstepper %}

## Creating and Editing a VLookup Transformation

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.

### Prerequisites

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

### Creating a VLookup Transformation

To create a VLookup transformation, complete the following steps.

{% stepper %}
{% step %}

#### Open the Transformations Panel

1. In the Syntactic Mappings page, choose the mapping you want to edit.
2. Click the Edit Pencil in the Transformation field for the entry you would like to edit transformations for.

![](/files/a754175e7b21424e70d1fcf273df9ac6bda529f5)

3. The Transformations Panel will appear. Click the Add Transformation button.

![](/files/3128ae08019b8cb78a12317a366137b570971b82)

4. Click the dropdown menu to select the desired transformation type.

![](/files/7daf74e237cd32e2e912e8830e483f215b582440)

5. Select the VLookup transformation.

![](/files/e427ff77318f9699d865a17bc3f658cbd52029b8)
{% endstep %}

{% step %}

#### Fill in the Transformation Configuration

1. Note the Transformation Input and Transformation Output. It should reflect what you already chose in the syntactic mapping entry.
2. Fill in the Lookup Table - this is a source data schema in which you want to match values from the source field.
3. 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.
4. 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.
5. 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).
6. 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 optional available: use the first match, use the last match, fail the code run, or set to a default value (return a number or string).
7. When complete, select the Save button.
8. Note that the source field values doesn’t need to be unique (the same value can appear multiple times in the source field and would be transformed to the same value in the output consistently).
   {% endstep %}
   {% endstepper %}

## Creating and Editing a Row Level Code Transformation

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.

### Prerequisites

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

### Creating a Row Level Code Transformation

To create a Row Level Code transformation, complete the following steps.

{% stepper %}
{% step %}

#### Step 1: Open the Transformations Panel

1. In the Syntactic Mappings page, choose the mapping you want to edit.
2. Click the Edit Pencil in the Transformation field for the entry you would like to edit transformations for.\
   ![](/files/5a734bfbe1ca33aa4b247356c2d696b86e4d3475)
3. The Transformations Panel will appear. Click the Add Transformation button.\
   ![](/files/703b7ec640de00f4d60d217d6324651186b355e0)
4. Click the dropdown menu to select the desired transformation type.

   ![](/files/02d4ea12898e440fb5791a094f5876b579d5d7b9)
5. Select the Row Level Code transformation.

   ![](/files/b30e4e250bbdd9f45ad24c46b0fb25a876ebbdd1)
   {% endstep %}

{% step %}

#### Step 2: Fill in the Transformation Configuration

1. Note the Source Input and Target Output. It should reflect what you already chose in the syntactic mapping entry.
2. 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:

     ```python
     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:

     ```python
     output = row["gender"][:1].upper()
     ```
3. 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.
4. When complete, click the Save button.
   {% endstep %}
   {% endstepper %}

## Creating and Editing a Table Level Code Transformation

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

### Prerequisites

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

### Creating a Table Level Code Transformation

To create a Table Level Code transformation, complete the following steps.

{% stepper %}
{% step %}

#### Open the Transformations Panel

1. In the Syntactic Mappings page, choose the mapping you want to edit.
2. Click the Edit Pencil in the Transformation field for the entry you would like to edit transformations for.\
   ![](/files/393d6887e2344037d0656ddffd65dfcfc529ed72)
3. The Transformations Panel will appear. Click the Add Transformation button.\
   ![](/files/dd95e108e1bf4febc28d0ea0673567a802e5b49e)
4. Click the dropdown menu to select the desired transformation type.

   ![](/files/20efbcbd719a293c60b39a2099876b6e3aa28473)
5. Select the Table Level Code transformation.

   ![](/files/1193b79ae4061fa4bbd75355e0fffa1b8919d596)
   {% endstep %}

{% step %}

#### Fill in the Transformation Configuration

1. Note the Source Input and Target Output. It should reflect what you already chose in the syntactic mapping entry.
2. 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)
     ```

{% endstep %}

{% step %}

#### 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 value can be a string or a number and it will be set in the output field for all rows.
{% endstep %}

{% step %}

#### Save

When complete, select the Save button.
{% endstep %}
{% endstepper %}