> For the complete documentation index, see [llms.txt](https://docs.sdv.dev/sdv/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.sdv.dev/sdv/modeling/constraint-augmented-generation-cag/predefined-constraints/columnformula.md).

# ❖ ColumnFormula

{% hint style="info" %}
❖ **SDV Enterprise Bundle**. This feature is available as part of the **CAG Bundle**, an optional add-on to SDV Enterprise. For more information, please visit the [CAG Bundle](/sdv/explore/sdv-bundles/cag.md) page.
{% endhint %}

Use the **ColumnFormula** constraint when one of the columns in your table can be directly, formulaically determined based on the values in some other columns.

<figure><img src="/files/jPySUVsdHmXJz5LFqdzs" alt=""><figcaption><p>This dataset contains information about guests checking into a hotel. The <code>total_rate</code> column can be formulaically determined from the <code>room_rate + amenities_fee</code>.</p></figcaption></figure>

## Formula Definition

To use the constraint, you would first need to define a formula function.

* Input: This function should take as input a pandas DataFrame containing all the columns that are needed for for the formula.
* Output: It should output a pandas Series object containing the formulaically-determined column.

In the example below, we're showing a simple example where the `total_rate = room_rate + amenities_fee`.

```python
def get_room_total(input):
  """ The total rate is the sum of room_rate and amenities_fee
  Input columns are room_rate, amenities_fee
  Output column is the total_rate
  """
  total_rate = input['room_rate'] + input['amenities_fee']
  return total_rate
```

Yo can write and save this function in a separate file, for example `my_formulas.py`. Or alternatively, you can also define it in the same script as your main SDV modeling and sampling.

## Constraint API

Create a `ColumnFormula` constraint.

**Parameters**:&#x20;

* (required) `input_column_names`: A list of strings that describe all the column names that should be inputs into the formula.\
  \&#xNAN;*In the previous SDV Enterprise version 0.47, this was called `input_columns`.*
* (required) `output_column_name`: A string that describes the final output column name. This is the column that is formulaically determined.\
  \&#xNAN;*In the previous SDV Enterprise version 0.47, this was called `output_column`.*
* (required) `formula_function_name`: A string that contains the name of the formula function. This should be defined within the Python file specified above.
* `module_name`: A string with the name of the Python module where the formula function is written. (This is typically the name of the Python file without the `.py` suffix.) \
  \&#xNAN;*In the previous SDV Enterprise version 0.47, this was called `module`.*
  * (default) `"__main__"`: The currently active module. Use this if the function is defined in the same file as the constraint.
* `table_name`: The name of the table that contains the columns. *This is required if you have multi-table data.*

```python
from sdv.cag import ColumnFormula

my_constraint = ColumnFormula(
    input_column_names=['room_rate', 'amenities_fee'],
    output_column_name='total_rate',
    formula_function_name='get_room_total',
    module_name='my_formulas',
)
```

Make sure that all the tables and columns you provide are in your [Metadata](/sdv/integration/metadata.md).

### Usage

Apply the constraint to any SDV synthesizer. Then fit and sample as usual.

```python
synthesizer = GaussianCopulaSynthesizer(metadata)
synthesizer.add_constraints([my_constraint])

synthesizer.fit(data)
synthetic_data = synthesizer.sample(num_rows=10)
```

For more information about using predefined constraints, please see the [**Constraint-Augmented Generation tutorial**](https://colab.research.google.com/drive/1WCMQujfVKL5giULZXOPPIBoMfzR9Zj68?usp=sharing).

## Auto-Detection <a href="#auto-detection" id="auto-detection"></a>

Auto-detection not available for this constraint. Please create the constraint object and add it to the synthesizer using the API described above.

## FAQs

<details>

<summary>Can I define the formula in the same file as my synthesizer and constraint?</summary>

Yes. If your formula is defined in the same file, the module name should be `"__main__"`, which is the keyword that Python uses for the currently active script. This is the default value for the parameter.

```python
my_constraint = ColumnFormula(
    input_column_names=['room_rate', 'amenities_fee'],
    output_column_name='total_rate',
    formula_function_name='get_room_total',
    module_name='__main__',
)
```

</details>

<details>

<summary>If I save a synthesizer with this constraint, can it work in other locations without the formula file?</summary>

Yes. As long as you have added the ColumnFormula constraint and fitted your synthesizer, then the saved synthesizer will always include the formula. You can load the synthesizer file in a different environment and sample synthetic data from it immediately. No need need to import or redefine the formula file.

For example, you can save the fitted synthesizer that contains the ColumnFormula constraint:

```python
synthesizer.save('my_formula_synthesizer.pkl')
```

You can then load the synthesizer later into a different environment and continue sampling synthetic data. This will work even if you're loading it in a different location without the formula file.

```python
from sdv.utils import load_synthesizer

synthesizer = load_synthesizer('my_synthesizer.pkl')
synthetic_data = synthesizer.sample(num_rows=10)
```

</details>

<details>

<summary>Can I define a formula that only applies partially to a subset of the data?</summary>

It's possible to have a formula that only applies to certain rows of your data. For other rows, you can allow SDV to algorithmically generate the data. For example:

* `total_rate = room_rate + amenities_fee` only if the hotel guest is a basic member
* If they are a star member, then the `total_rate` does not follow this logic. SDV can algorithmically generate it.

In this case, you can define a function that takes in the final output column (`total_rate`) as part of the input too. The input will contain the algorithmically generated values from the synthesizer, which may not necessarily match the formula. Use these values as a starting point; fix them for any rows that need it.

```python
def get_room_total(input):
  # the algorithmically-determined total is available in the total_rate column
  # this does not necessarily follow the formula for basic members
  original_rate = input['total_rate'] 

  # calculate the what the rate should be for basic members based on the sum
  membership_mask = (input['membership_type'] == 'BASIC') 
  rate_sum = input['room_rate'] + input['amenities_fee']
  
  # only update the total rate when the member is a basic member
  total_rate = original_rate.where(membership_mask, rate_sum)
  
  return total_rate
```

When defining the constraint, make sure you include the output column in the input column list as well.

```python
# Creating the constraint
from sdv.cag.sandbox import ColumnFormula

my_constraint = ColumnFormula(
    input_column_names=['room_rate', 'amenities_fee', 'total_rate', 'membership_type'],
    output_column_name='total_rate',
    module_name='my_formulas',
    formula_function_name='get_room_total'
)
```

Please be aware that while partial formulas like this are supported, this constraint is not optimized for them. You may notice some impacts to the synthetic data quality of the output column. For troubleshooting support, [please reach out](/sdv/support/troubleshooting/sdv-usage.md#other-issues) to us. *We are able to provide support for SDV Enterprise customers who have purchased the CAG bundle.*

</details>


---

# 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.sdv.dev/sdv/modeling/constraint-augmented-generation-cag/predefined-constraints/columnformula.md?ask=<question>&goal=<endgoal>
```

`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.