> For the complete documentation index, see [llms.txt](https://docs.bdb.ai/data-center-3/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.bdb.ai/data-center-3/data-center/data-preparation/data-preparation-workspace/transforms/advanced.md).

# Advanced

## Cluster & Edit

Find out the clusters based on the pronunciation sound and edit the bulk data in a single click.

{% hint style="success" %}
*Check out the given illustration on the Cluster & Edit transform.*
{% endhint %}

{% embed url="<https://files.gitbook.com/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FIctE5LjGWDD6zEdW4vpJ%2Fuploads%2FJ3shB7jRNpYZOpy7HFjH%2FAdvanced_Cluster%26edit.mp4?alt=media&token=e5be27e7-f355-45d0-8597-1c655a57fda5>" %}
***Cluster & Edit Transform***
{% endembed %}

Steps to perform the ***Cluster & Edit*** transform.

* Select a column from the given dataset.
* Open the ***Transforms*** tab.
* Select the ***Cluster and Edit*** transform from the Advanced category.

<figure><img src="https://content.gitbook.com/content/IctE5LjGWDD6zEdW4vpJ/blobs/mkP8vWr7uxCJ7ywunwiy/image.png" alt=""><figcaption></figcaption></figure>

* The ***Cluster & Edit*** window opens.
* The ***Method*** drop-down uses the Soundex phonetic algorithm for indexing names by sound as pronounced in English.
* The ***Values found*** column lists number of values found from the data set related to a specific sound. E.g., In the given image the Values found display 5 categories.
* Select a value by using the checkbox that needs to be modified or changed. E.g., the '***Declineed***' has been selected in the given example.
* Navigate to the ***Replace Value*** list.
* Search for a replace value or enter a value that you wish to be used as replace value using the drop-down menu from the ***Replace Value*** column.
* Click the ***Submit*** option.

<figure><img src="https://content.gitbook.com/content/IctE5LjGWDD6zEdW4vpJ/blobs/DKz2SITUzufT9dU2WDuX/image.png" alt=""><figcaption></figcaption></figure>

* The selected values from the column get modified in the data set.

<figure><img src="https://content.gitbook.com/content/IctE5LjGWDD6zEdW4vpJ/blobs/9KJxrqiv6KhkSIIAEWTh/image.png" alt=""><figcaption></figcaption></figure>

## Expression Editor

This transform helps to execute expressions.

{% hint style="success" %}
*Check out the given illustration to understand the Expression Editor transform.*
{% endhint %}

{% embed url="<https://files.gitbook.com/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FIctE5LjGWDD6zEdW4vpJ%2Fuploads%2FpwaWLfr9KskouXo9T9KY%2FAdvanced_ExpressionEditor.mp4?alt=media&token=51be8d80-b047-4a7a-9393-f8c521d052e7>" %}
Expression Editor Transform
{% endembed %}

Steps to perform the ***Expression Editor*** transform:

* Navigate to a Dataset within the ***Data Preparation*** framework.
* Navigate to the ***Transforms*** tab.
* Open the ***Expression Editor*** from the ***Advanced*** transforms.

<figure><img src="https://content.gitbook.com/content/IctE5LjGWDD6zEdW4vpJ/blobs/m2tRKiEplWQDIoDTLoWr/image.png" alt=""><figcaption></figcaption></figure>

* The Expression Editor window opens displaying the following columns:
  * Functions: The first column contains **functions** for the user to search for a function. By using the double clicks on a function, it gets added to the given space provided for creating a formula.
  * Columns: The second column lists all the column names available in the selected dataset.
* The ***Formula*** space is provided to create and execute various formulas/ executions.&#x20;
* Use either of the following ways to consume the created expression or formula in the dataset.
  * Update a selected column by using the ***Update column*** option.
  * Create a new column with the created expression, and provide column name for the ***New Column***. A new column has been created in the example given below:
* Click the ***Submit*** option to either add a new column or update the selected column based on the executed formula/ expression.

<figure><img src="https://content.gitbook.com/content/IctE5LjGWDD6zEdW4vpJ/blobs/y35QOBTe2VosuZCVI7xD/image.png" alt=""><figcaption></figcaption></figure>

* The recently created or updated column with Formula gets added to the dataset.

<figure><img src="https://content.gitbook.com/content/IctE5LjGWDD6zEdW4vpJ/blobs/xnDWqrofob1QA5BDnQNm/image.png" alt=""><figcaption></figcaption></figure>

## Find Anomaly

Anomaly detection is used to identify any anomaly present in the data. i.e., Outlier.  Instead of looking for usual points in the data, it looks for any anomaly. It uses the ***Isolation Forest*** algorithm.

{% hint style="success" %}
*Check out the given walk-through on Find Anomaly transform.*
{% endhint %}

{% embed url="<https://files.gitbook.com/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FIctE5LjGWDD6zEdW4vpJ%2Fuploads%2FxPazNtFFO1BbCnf1g6PR%2FAdvanced_findAnomaly.mp4?alt=media&token=3cfd0056-f33c-45f9-88be-2ae9ade3d605>" %}
***Find Anomaly Transform***
{% endembed %}

Steps to perform the ***Find Anomaly*** transform:

* Select a dataset within the ***Data Preparation*** framework.&#x20;
* Navigate to the ***Transforms*** tab.
* Select the ***Find Anomaly*** transform from the ***ADVANCED*** category.
* Configure the following information:
  * Select ***Feature Columns***: Select one or more columns where you want to find the anomaly.
  * ***Maximum Sample Size***: The ***Isolation Forest*** algorithm takes the training data of a given sample size to find out the normal value in the dataset.
  * ***Contamination*** (%): It is the percentage of observations we believe to be outliers. It varies from 0 to 1 (both inclusive).
  * ***Anomaly Flag Name***: The result is either -1 or 1. 1 means the data is standard, and -1 means data is an outlier. This information gets stored in the new column given in the anomaly flag name.
* Click the ***Submit*** option after the required details are provided.
* The anomaly gets flagged under the column that has been named using the ***Anomaly Flag Name*** option.&#x20;

<figure><img src="https://content.gitbook.com/content/IctE5LjGWDD6zEdW4vpJ/blobs/ifnaIFZ4kHliVv6H5sGM/image.png" alt=""><figcaption></figcaption></figure>

{% hint style="info" %}
*<mark style="color:green;">Please Note:</mark> The other needed parameters such as **Estimators** and **seed values** are considered based on their default values to run the **Isolation Forest logic** on the selected dataset sample.*
{% endhint %}

## SQL Transform

This transform helps to perform SQL queries.

{% hint style="success" %}
*Check out the given illustration on the SQL Transform.*
{% endhint %}

{% embed url="<https://files.gitbook.com/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FIctE5LjGWDD6zEdW4vpJ%2Fuploads%2Ft6cwKhVLNH6GkWUT82TD%2FAdvanced_SQL%20transform.mp4?alt=media&token=5a5b64bf-83d3-4ba1-b515-da6b018b8c6b>" %}
***SQL Transform***
{% endembed %}

Steps to perform the ***SQL*** transform:

* Select a dataset within the Data Preparation framework.
* Navigate to the ***Transforms*** tab.
* Open the ***SQL Transform*** from the ***Advanced*** transforms.

{% hint style="info" %}
*<mark style="color:green;">Please Note:</mark> Function syntax and small example comes under the text area by using double-clicks on the functions.*
{% endhint %}

* Click the ***Submit*** option to add a new column based on the query result.

<figure><img src="https://content.gitbook.com/content/IctE5LjGWDD6zEdW4vpJ/blobs/8pJBjKbVfB9KJpbzURhV/image.png" alt=""><figcaption></figcaption></figure>

* The ***SQL Editor*** page opens.
* The ***First column*** contains **SQL functions,** the user can search for a function and add it to the given text space provided for writing query.
* The ***Second column*** lists all the column names available in the dataset.
* The ***Text area*** is provided for writing queries.
* Based on the selected function an example will be displayed below the text space.
* Click the ***Submit*** option.

<figure><img src="https://content.gitbook.com/content/IctE5LjGWDD6zEdW4vpJ/blobs/mQdtru0pdQK8uhuY4Iaq/image.png" alt=""><figcaption></figcaption></figure>

* A new column gets added to the dataset reflecting the condition provided through the SQL transform.

<figure><img src="https://content.gitbook.com/content/IctE5LjGWDD6zEdW4vpJ/blobs/1FxexfckRJTUAiP2UL5p/image.png" alt=""><figcaption></figcaption></figure>

{% hint style="info" %}
*<mark style="color:green;">Please Note:</mark> The **SQL Transform** & **Expression Editor** support only **Pandas SQL Queries.***
{% endhint %}