> 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/syntactic-mapping/column-transformations.md).
# Column Transformations
## Overview
Once you have [created a syntactic mapping](/data-harmonization/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 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) |
| --------------------------- | -------------------------------- |
| 1 | 8527 |
| 2 | 4212311 |
| 3 | 8515 |
| 4 | 38003598 |
| 5 | 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](https://app.gitbook.com/o/SKrkLtXReIPmOZjXnWZA/s/ydySyCBmy6F7NnGn4bPa/~/edit/~/changes/340/data-harmonization/syntactic-mapping/column-transformations#transformation-types) 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
{% stepper %}
{% step %}
#### Select the syntactic 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 %}
#### 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.
{% endstep %}
{% step %}
#### 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.
{% hint style="info" %}
When code runs, configured transformations are executed one after another for a given syntactic mapping entry.
{% endhint %}
{% endstep %}
{% step %}
#### Save the changes
When complete, click the "Save" button.
{% endstep %}
{% endstepper %}
{% hint style="warning" %}
Values that cannot be converted are left empty in the output. See the code run logs for a breakdown of conversion failures.
{% endhint %}
***
## Transformation Types
| Transformation Type | Description |
| --------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| [Custom Mapping](#transformation-type-custom-mapping) |
Maps values from the source dataset to corresponding values in the target dataset
e.g., convert a date from MM/DD/YY format to YYYY-MM-DD.
|
| [Stable UUID](#transformation-type-stable-uuid) | Generates an encrypted unique identifier based on the input. The same input will always generate the same unique identifier. |
| [Row Level Code](#transformation-type-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](#transformation-type-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](#transformation-type-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.
{% stepper %}
{% step %}
Follow the steps [here](#steps) to access the transformation panel.
{% endstep %}
{% step %}
Select the **Custom Mapping** transformation.
{% endstep %}
{% step %}
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"
}
```
{% 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 %}
### **Transformation Type:** Semantic Mapping
A **Semantic Mapping** transformation applies a [semantic mapping](/data-harmonization/semantic-mapping.md) 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.
{% stepper %}
{% step %}
Follow the steps [here](#steps) to access the transformation panel.
{% endstep %}
{% step %}
Select the **Semantic Mapping** transformation.
{% endstep %}
{% step %}
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
{% endstep %}
{% step %}
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.
{% endstep %}
{% step %}
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/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.
{% endstep %}
{% step %}
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.
{% endstep %}
{% step %}
When complete, click the "Save" button.
{% endstep %}
{% endstepper %}
### **Transformation Type:** Set Value
A **Set Value** transformation sets a specific value in all rows of the output field.
{% stepper %}
{% step %}
Follow the steps [here](#steps) to access the transformation panel.
{% endstep %}
{% step %}
Select the **Set Value** transformation.
{% endstep %}
{% step %}
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.
{% endstep %}
{% step %}
When complete, click the "Save" button.
{% endstep %}
{% endstepper %}
### **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` |
{% hint style="info" %}
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.
{% endhint %}
{% stepper %}
{% step %}
Follow the steps [here](#steps) to access the transformation panel.
{% endstep %}
{% step %}
Select the **Convert Date** transformation.
{% endstep %}
{% step %}
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".
{% endstep %}
{% step %}
When complete, click the "Save" button.
{% endstep %}
{% endstepper %}
### **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.
{% stepper %}
{% step %}
Follow the steps [here](#steps) to access the transformation panel.
{% endstep %}
{% step %}
Select the **Stable UUID** transformation.
{% endstep %}
{% step %}
There is no additional configuration required for the Stable UUID transformation.
{% endstep %}
{% step %}
When complete, click the "Save" button.
{% endstep %}
{% endstepper %}
### **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.
{% stepper %}
{% step %}
Follow the steps [here](#steps) to access the transformation panel.
{% endstep %}
{% step %}
Select the **Row Level Code** transformation.
{% endstep %}
{% step %}
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()
```
{% endstep %}
{% step %}
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.
{% endstep %}
{% step %}
When complete, click the "Save" button.
{% endstep %}
{% endstepper %}
### **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.
{% stepper %}
{% step %}
Follow the steps [here](#steps) to access the transformation panel.
{% endstep %}
{% step %}
Select the **Table Level Code** transformation.
{% endstep %}
{% step %}
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 default value can be a string or a number and it will be set in the output field for all rows.
{% endstep %}
{% step %}
When complete, click the "Save" button.
{% endstep %}
{% endstepper %}
### **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.
{% stepper %}
{% step %}
Follow the steps [here](#steps) to access the transformation panel.
{% endstep %}
{% step %}
Select the **VLookup** transformation.
{% endstep %}
{% step %}
Fill in the **Lookup Table** - this is a source data schema in which you want to match values from the source field.
{% endstep %}
{% step %}
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.
{% endstep %}
{% step %}
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.
{% endstep %}
{% step %}
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).
{% endstep %}
{% step %}
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).
{% endstep %}
{% step %}
When complete, click the "Save" button.
{% endstep %}
{% endstepper %}
{% hint style="info" %}
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).
{% endhint %}
---
# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.
## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.
Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:
```
GET https://docs.rhinofcp.com/data-harmonization/syntactic-mapping/column-transformations.md?ask=&goal=
```
`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.
Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.
