Using Python expressions to process numeric columns

Using Python expressions to process numeric columns#

This guide explains how to transform numeric columns in a data extract.

Introduction#

Use Python expressions in custom scripts to transform your data extract. For a list of all available custom script transformations, see Available custom script instructions.

When using Python expressions in custom scripts, you must follow certain rules - these rules are covered in more detail in Using Python expressions in custom scripts.

In Adverity, when working with numeric data, you may need to use the:doc:enrichment-reference/convertnumbers instruction before other instructions that require numeric data. Add the convertnumbers instruction to the same custom script transformation before the instruction that requires data in numeric format.

Handling missing values#

To replace missing values with zero, enter the following Python expression into the transformation:

{column_name} if {column_name} is not None else 0

To configure the Python expression, change the following parameters:

  • column_name - This is the name of the column that you want to process.

Converting scientific notation to Float#

In some cases, a value is pulled in a scientific format (e+X). For example, if the original value is: 23843289494680100 it becomes 2.38432894947e+16.

To convert the values back to Float, use the convertx custom script with the following Python expression:

format(float({column_name}), 'f').split('.')[0]

To configure the Python expression, change the following parameters:

  • column_name - This is the name of the column that you want to convert.

Converting formatted numeric columns#

Formatted numbers, such as currency columns, are usually treated as string values instead of numeric.

To convert formatted numeric values to numeric format, follow these steps:

  1. Remove the currency sign with convertx custom script and the following Python expression:

    str({column_name}.replace('currency_sign', ''))

    To configure the Python expression, change the following parameters:

    • column_name - This is the name of the column that you want to convert.

    • currency_sign - This is the symbol of the currency used in the column.

  2. Use the convertnumbers custom script to convert the column to numeric format.

Calculating ratios#

To calculate ratios based on your data, enter the following Python expression into the transformation:

column_name_1 / column_name_2 if column_name_2 != 0 else 0

To configure the Python expression, change the following parameters:

  • column_name_1 and column_name_2 - These are the names of the columns that you want to use in your calculation.

Contents