Script Operator Python Reference

Learn about the Python support offered by the data flow script operator.

Support for builtins, Numpy, and Pandas Modules

The script operator offers support for data manipulation and vector operations via support for the builtins (version 3.9), NumPy (version 1.21.5), and Pandas (version 1.2.5) modules. In order to achieve a safe execution environment, some restrictions are applied.

Builtin Module (version 3.9)

NumPy Module (version 1.21.5)

Pandas Module (version 1.2.5)

All constants and functions in the Python builtins module are supported except the following:

  • eval

  • exec

  • compile

  • classmethod

  • staticmethod

  • breakpoint

  • memoryview

  • __import__() i.e. import statement

  • input

  • open

  • dir

  • object

  • print

  • id

  • vars

  • hasattr

  • getattr and __getattribute__

  • setattr and __setattr__

  • delattr and __delattr__

All objects and functions in the NumPy module are supported (using the standard np alias) except the following:
  • load

  • save

  • savez

  • savez_compressed

  • loadtxt

  • savetxt

  • genfromtxt

  • fromregex

  • fromfile

  • memmap

  • DataSource

  • ndarray.tofile

  • ndarray.dump

All objects and functions in the Pandas module are supported (using the standard pd alias) except the following:
  • read_pickle

  • read_table

  • read_csv

  • read_fwf

  • read_clipboard

  • read_excel

  • read_json

  • read_html

  • read_hdf

  • read_feather

  • read_parquet

  • read_sas

  • read_spss

  • read_gbq

  • read_stata

  • read_sql_table

  • read_sql_query

  • io.excel.ExcelFile

  • io.excel.ExcelWriter

  • io.stata.StataReader

  • io.sql.SQLDatabase

  • io.sql.SQLiteDatabase

  • io.pytables.HDFStore

  • DataFrame.to_pickle

  • DataFrame.to_csv

  • DataFrame.to_hdf

  • DataFrame.to_sql

  • DataFrame.to_excel

  • DataFrame.to_html

  • DataFrame.to_feather

  • DataFrame.to_stata

  • DataFrame.to_gbq

  • DataFrame.to_clipboard

  • DataFrame.to_json

  • DataFrame.to_latex

  • DataFrame.to_markdown

  • Series.to_pickle

  • Series.to_csv

  • Series.to_excel

  • Series.to_hdf

  • Series.to_sql

  • Series.to_clipboard

  • Series.to_json

  • Series.to_latex

  • Series.to_markdown

Support for builtins Exception Types

The following builtin exception types are supported for use in error handling.
  • ArithmeticError

  • AssertionError

  • AttributeError

  • BufferError

  • FloatingPointError

  • IndexError

  • KeyError

  • LookupError

  • NameError

  • NotImplementedError

  • OverflowError

  • RuntimeError

  • StopIteration

  • TypeError

  • UnboundLocalError

  • UnicodeDecodeError

  • UnicodeEncodeError

  • UnicodeError

  • UnicodeTranslateError

  • ValueError

  • ZeroDivisionError

Data Mapping

The incoming table from the input port is converted to a Pandas DataFrame before being passed as the data parameter to the transform function. Similarly, the table returned from the user code is converted from a Pandas DataFrame to the data types common to all the operators.

You can find the correspondence between the supported column data types and Python objects in the table below.

Column Data Type Python Class dtype Restrictions Observation

Binary

byte

object

   

Boolean

numpy.bool

bool

   

Date

pandas.Timestamp

datetime64[ns]

Can only represent range: 1677-09-21 to 2262-04-11

 

DateTime

pandas.Timestamp

datetime64[ns]

Can only represent range: 1677-09-21 00:12:43.145225 to 2262-04-11 23:47:16.854775807

 

Decimal

decimal.Decimal

object

   

DecimalFloat

decimal.Decimal

object

   

Integer

int

int64*

 

*The smallest possible between uint8, uint16, uint32, and uint64 that can hold all values.

Integer64

int

int64

   

LargeBinary

bytes

object

   

LargeString

string

object

   

String

string

object

   

Time

pandas.Timestamp

datetime64[ns]

   

TimeStamp

pandas.Timestamp

datetime64[ns]

Can only represent range: 1677-09-21 00:12:43.145225 to 2262-04-11 23:47:16.854775807

 

Basic Examples

Type of incoming data is restricted to Pandas DataFrame.

Example 1

This script creates a projection from an incoming DataFrame. It assumes that there would be at least these three columns in the incoming DataFrame: first, customer_name, and office_location. That is an alternative way to say that the input table has at least these three attributes. The projection script then extracts only these three columns and returns them as a DataFrame.

Input table (Example DataFrame stored locally in the script as data):
first last birthday customer_name office_location

John

Doe

2002-04-09 12:33:58

RedBull

Walldorf

Jane

Doe

2005-03-03 12:33:58

GoPro

São Leopoldo

Output table (Example returning DataFrame projection):
first customer_name office_location

John

RedBull

Walldorf

Jane

GoPro

São Leopoldo

It expects that the output table has only these three attributes, first, customer_name, and office_location. If the output table schema is different in any ways from this, the data flow execution fails.

Example 2

This script extracts the 'first' and 'last' columns of the incoming DataFrame, assuming that they exist in the input table, and stores these two columns as a separate DataFrame in the local variable 'df'. It then computes a third column email from the extracted columns, updates df, and returns it.

Input table (Example DataFrame stored locally in the script as df):
first last

John

Doe

Jane

Doe

Output table (Example returning DataFrame df):
first last full

John

Doe

John.Doe

Jane

Doe

Jane.Doe

The data flow would fail if the input table has neither the first nor the last attribute, among others. Similarly, it would also fail if the output table schema does not consist of just these three attributes, first, last, and full.

Example 3

This script assumes that there is a birthday column among others, with the string values in the incoming DataFrame. It first attempts to convert the string values of this column to pandas Timestamp and store as a Series. Finally, it makes a specific string representation of the birth days in the Series, using the methods and attributes from the Timestamp class, and updates the same in the original DataFrame, before returning the same.

Input table (Example of incoming DataFrame as data):
first last birthday

John

Doe

2002-04-09 12:33:58

Jane

Doe

2005-03-03 12:33:58

Output table (Example returning DataFrame as data):
first last birthday

John

Doe

April 9, 2002, Tuesday

Jane

Doe

March 3, 2005, Thursday

In this case, it assumes that the input and output tables have the same schema and data types.

Handling Decimal Types

To work with decimal types in the script operator, the built-in Python Decimal class should be used. There is no way or need to import it, as it is available for the user inside the body of the transform function. Few examples of using the Decimal type in the script operator are provided below.

Example 4

This script assumes that the input table has a column name dept_no and dept_name and it wants to add decimal type arbitrary column start_date_utc_long to it. The actual data type precision of this column is assumed to be decimal(21,0) in the output table definition.

Input table (Example of incoming DataFrame as data):
dept_no dept_name

dept_101

Audit

dept_102

Advisory

Output table (Example returning DataFrame as data):
dept_no dept_name start_date_utc_long

dept_101

Audit

Decimal("123456789012345678901")

dept_102

Advisory

Decimal("123456789012345678901")

In this case, it assumes that the input and output tables have the same schema and data types.

Example 5

This script assumes that the input table has a column name salary and this column is of data type Decimal. The script applies a filter on the column salary comparing the value of its contents to a python Decimal object and returns the filtered table.

Input table (Example of incoming DataFrame as data):
first last salary

John

Doe

Decimal("1099.99")

Jane

Doe

Decimal("1599.50")

Output table (Example returning DataFrame as data):
first last salary

Jane

Doe

Decimal("1599.50")

The example assumes that the input and output tables have the same schema and data types.