❖ ColumnFormula

❖ 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 page.

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.

This dataset contains information about guests checking into a hotel. The total_rate column can be formulaically determined from the room_rate + amenities_fee.

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.

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:

• (required) input_column_names: A list of strings that describe all the column names that should be inputs into the formula. 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. 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.) 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.

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.

Usage

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

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.

Auto-Detection

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

FAQs

Can I define the formula in the same file as my synthesizer and constraint?

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.

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

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

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:

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.

from sdv.utils import load_synthesizer

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

Can I define a formula that only applies partially to a subset of the data?

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.

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.

# 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 to us. We are able to provide support for SDV Enterprise customers who have purchased the CAG bundle.