Action Catalogue

Aggregation

Aggregate columns within groups

Unique identifier (API): aggregate

Group by the selected columns and compute aggregations within each group

Parameters	Parameter Description	Default	Parameter Type
merge_back_to_original	Whether to merge the aggregation(s) back to the original data	False	boolean

Examples:

Given the dataset

grp	val	val1	bool1	datetimes
1	0.45	1	True	2023-11-17T13:08:29
1	0.45	2	True	2023-11-17T13:08:29
3	0.45	3	True	2023-11-17T13:08:29
3	0.45	3.5	True	2023-08-03T08:48:29
3	1	3	False	2022-05-29T03:21:59
3	1	4	False	2022-05-29T03:21:59
2	10	5	False	2022-05-29T03:21:59
2	100	6	False	2022-05-29T03:21:59

If we group by grp and peform the following aggregations within each group

• mean(val)
• sum(val1)
• all(bool1)
• any(bool)
• approx_count_distinct(val, 0)
• approx_count_distinct(bool1, 0)
• earliest(datetimes, 0)
• latest(datetimes, 0)
• first(val, 0)
• last(val, 0)
• approx_median(val)
• approx_quantile(val)
• count(bool1, "non_null")
• count(bool1, "null)

we get

grp	avg_val	sum_val1	all_bool1	any_bool1	approx_count_distinct_val	approx_count_distinct_bool1	earliest_datetime	latest_datetime	first_val	last_val	median_val	quantile_val	count_bool1	count_nulls_bool1
Text	Numeric	Numeric	Boolean	Boolean	Numeric	Numeric	DateTime	DateTime	Numeric	Numeric	Numeric	Numeric	Numeric	Numeric
1	0.45	3.0	True	True	1	1	2023-11-17 13:08:29	2023-11-17 13:08:29	0.45	0.45	0.45	0.45	2	0
2	55	11	False	False	2	1	2022-05-29 03:21:59	2022-05-29 03:21:59	10	100	10.0	10	2	0
3	0.725	13.5	False	True	2

The user may optionally specify merge_back_to_original = True in which case the group by results will be joined with the original table using the group by columns as the join keys. Note: the resultant table using merge_back_to_original = True will have the same number of rows as the original table.

Casting

Cast columns to categorical type

Unique identifier (API): cast_categorical

Cast the selected columns to categorical type.

Parameters	Parameter Description	Default	Parameter Type
categories_specified	If set to 'true', categories must be specified explicitly. If set to 'false', every unique value in column will become a category	False	boolean
categories	Categories in the column		string-list

Cast categorical columns to text type

Unique identifier (API): cast_categorical_to_text

Cast selected categorical columns to text type

Cast columns to DateTime type

Unique identifier (API): cast_datetime

Cast the selected columns to DateTime type with specified format string

Parameters	Parameter Description	Default	Parameter Type	Suggested Values
datetime_format	DateTime format string in the strftime format.	%Y-%m-%dT%H:%M:%S.%fZ	string	['%Y-%m-%dT%H:%M:%S.%f%z', '%Y-%m-%dT%H:%M:%S.%fZ', '%b %d %Y %H:%M:%S.%f', '%Y-%m-%dT%H:%M:%S.%f', '%m-%d-%Y %H:%M:%S.%f', '%d-%m-%Y %H:%M:%S.%f', '%d %b %Y %H:%M:%S.%f', '%Y/%m/%d %H:%M:%S.%f', '%Y-%m-%d %H:%M:%S.%f', '%d/%m/%Y %H:%M:%S.%f', '%Y-%m-%dT%H:%M:%S%z', '%Y-%m-%dT%H:%M:%SZ', '%d%m%Y %H:%M:%S.%f', '%m%d%Y %H:%M:%S.%f', '%Y%m%d %H:%M:%S.%f', '%m-%d-%Y %H:%M:%S', '%Y/%m/%d %H:%M:%S', '%Y-%m-%dT%H:%M:%S', '%b-%d-%Y %H:%M:%S', '%d-%m-%Y %H:%M:%S', '%d-%b-%Y %H:%M:%S', '%d/%m/%Y %H:%M:%S', '%Y-%m-%d %H:%M:%S', '%m/%d/%Y %I:%M:%S %p', '%Y%m%d %H:%M:%S', '%Y-%m-%dT%H:%MZ', '%m%d%Y %H:%M:%S', '%d%m%Y %H:%M:%S', '%m-%d-%Y', '%Y-%m-%d', '%d-%m-%Y', '%m %d %Y', '%d-%b-%Y', '%Y/%m/%d', '%d %m %Y', '%m/%d/%Y', '%Y %m %d', '%d/%m/%Y', '%d %b %Y', '%b-%d-%Y', '%b %d %Y', '%m%d%Y', '%Y%m%d', '%d%m%Y', '%m%Y', '%m %Y', '%m-%Y', '%m/%Y']
timezone	Specifies the timezone the DateTimes belong to	UTC	string	['Africa/Abidjan', 'Africa/Accra', 'Africa/Addis_Ababa', 'Africa/Algiers', 'Africa/Asmara', 'Africa/Bamako', 'Africa/Bangui', 'Africa/Banjul', 'Africa/Bissau', 'Africa/Blantyre', 'Africa/Brazzaville', 'Africa/Bujumbura', 'Africa/Cairo', 'Africa/Casablanca', 'Africa/Ceuta', 'Africa/Conakry', 'Africa/Dakar', 'Africa/Dar_es_Salaam', 'Africa/Djibouti', 'Africa/Douala', 'Africa/El_Aaiun', 'Africa/Freetown', 'Africa/Gaborone', 'Africa/Harare', 'Africa/Johannesburg', 'Africa/Juba', 'Africa/Kampala', 'Africa/Khartoum', 'Africa/Kigali', 'Africa/Kinshasa', 'Africa/Lagos', 'Africa/Libreville', 'Africa/Lome', 'Africa/Luanda', 'Africa/Lubumbashi', 'Africa/Lusaka', 'Africa/Malabo', 'Africa/Maputo', 'Africa/Maseru', 'Africa/Mbabane', 'Africa/Mogadishu', 'Africa/Monrovia', 'Africa/Nairobi', 'Africa/Ndjamena', 'Africa/Niamey', 'Africa/Nouakchott', 'Africa/Ouagadougou', 'Africa/Porto-Novo', 'Africa/Sao_Tome', 'Africa/Tripoli', 'Africa/Tunis', 'Africa/Windhoek', 'America/Adak', 'America/Anchorage', 'America/Anguilla', 'America/Antigua', 'America/Araguaina', 'America/Argentina/Buenos_Aires', 'America/Argentina/Catamarca', 'America/Argentina/Cordoba', 'America/Argentina/Jujuy', 'America/Argentina/La_Rioja', 'America/Argentina/Mendoza', 'America/Argentina/Rio_Gallegos', 'America/Argentina/Salta', 'America/Argentina/San_Juan', 'America/Argentina/San_Luis', 'America/Argentina/Tucuman', 'America/Argentina/Ushuaia', 'America/Aruba', 'America/Asuncion', 'America/Atikokan', 'America/Bahia', 'America/Bahia_Banderas', 'America/Barbados', 'America/Belem', 'America/Belize', 'America/Blanc-Sablon', 'America/Boa_Vista', 'America/Bogota', 'America/Boise', 'America/Cambridge_Bay', 'America/Campo_Grande', 'America/Cancun', 'America/Caracas', 'America/Cayenne', 'America/Cayman', 'America/Chicago', 'America/Chihuahua', 'America/Costa_Rica', 'America/Creston', 'America/Cuiaba', 'America/Curacao', 'America/Danmarkshavn', 'America/Dawson', 'America/Dawson_Creek', 'America/Denver', 'America/Detroit', 'America/Dominica', 'America/Edmonton', 'America/Eirunepe', 'America/El_Salvador', 'America/Fort_Nelson', 'America/Fortaleza', 'America/Glace_Bay', 'America/Goose_Bay', 'America/Grand_Turk', 'America/Grenada', 'America/Guadeloupe', 'America/Guatemala', 'America/Guayaquil', 'America/Guyana', 'America/Halifax', 'America/Havana', 'America/Hermosillo', 'America/Indiana/Indianapolis', 'America/Indiana/Knox', 'America/Indiana/Marengo', 'America/Indiana/Petersburg', 'America/Indiana/Tell_City', 'America/Indiana/Vevay', 'America/Indiana/Vincennes', 'America/Indiana/Winamac', 'America/Inuvik', 'America/Iqaluit', 'America/Jamaica', 'America/Juneau', 'America/Kentucky/Louisville', 'America/Kentucky/Monticello', 'America/Kralendijk', 'America/La_Paz', 'America/Lima', 'America/Los_Angeles', 'America/Lower_Princes', 'America/Maceio', 'America/Managua', 'America/Manaus', 'America/Marigot', 'America/Martinique', 'America/Matamoros', 'America/Mazatlan', 'America/Menominee', 'America/Merida', 'America/Metlakatla', 'America/Mexico_City', 'America/Miquelon', 'America/Moncton', 'America/Monterrey', 'America/Montevideo', 'America/Montserrat', 'America/Nassau', 'America/New_York', 'America/Nipigon', 'America/Nome', 'America/Noronha', 'America/North_Dakota/Beulah', 'America/North_Dakota/Center', 'America/North_Dakota/New_Salem', 'America/Nuuk', 'America/Ojinaga', 'America/Panama', 'America/Pangnirtung', 'America/Paramaribo', 'America/Phoenix', 'America/Port-au-Prince', 'America/Port_of_Spain', 'America/Porto_Velho', 'America/Puerto_Rico', 'America/Punta_Arenas', 'America/Rainy_River', 'America/Rankin_Inlet', 'America/Recife', 'America/Regina', 'America/Resolute', 'America/Rio_Branco', 'America/Santarem', 'America/Santiago', 'America/Santo_Domingo', 'America/Sao_Paulo', 'America/Scoresbysund', 'America/Sitka', 'America/St_Barthelemy', 'America/St_Johns', 'America/St_Kitts', 'America/St_Lucia', 'America/St_Thomas', 'America/St_Vincent', 'America/Swift_Current', 'America/Tegucigalpa', 'America/Thule', 'America/Thunder_Bay', 'America/Tijuana', 'America/Toronto', 'America/Tortola', 'America/Vancouver', 'America/Whitehorse', 'America/Winnipeg', 'America/Yakutat', 'America/Yellowknife', 'Antarctica/Casey', 'Antarctica/Davis', 'Antarctica/DumontDUrville', 'Antarctica/Macquarie', 'Antarctica/Mawson', 'Antarctica/McMurdo', 'Antarctica/Palmer', 'Antarctica/Rothera', 'Antarctica/Syowa', 'Antarctica/Troll', 'Antarctica/Vostok', 'Arctic/Longyearbyen', 'Asia/Aden', 'Asia/Almaty', 'Asia/Amman', 'Asia/Anadyr', 'Asia/Aqtau', 'Asia/Aqtobe', 'Asia/Ashgabat', 'Asia/Atyrau', 'Asia/Baghdad', 'Asia/Bahrain', 'Asia/Baku', 'Asia/Bangkok', 'Asia/Barnaul', 'Asia/Beirut', 'Asia/Bishkek', 'Asia/Brunei', 'Asia/Chita', 'Asia/Choibalsan', 'Asia/Colombo', 'Asia/Damascus', 'Asia/Dhaka', 'Asia/Dili', 'Asia/Dubai', 'Asia/Dushanbe', 'Asia/Famagusta', 'Asia/Gaza', 'Asia/Hebron', 'Asia/Ho_Chi_Minh', 'Asia/Hong_Kong', 'Asia/Hovd', 'Asia/Irkutsk', 'Asia/Jakarta', 'Asia/Jayapura', 'Asia/Jerusalem', 'Asia/Kabul', 'Asia/Kamchatka', 'Asia/Karachi', 'Asia/Kathmandu', 'Asia/Khandyga', 'Asia/Kolkata', 'Asia/Krasnoyarsk', 'Asia/Kuala_Lumpur', 'Asia/Kuching', 'Asia/Kuwait', 'Asia/Macau', 'Asia/Magadan', 'Asia/Makassar', 'Asia/Manila', 'Asia/Muscat', 'Asia/Nicosia', 'Asia/Novokuznetsk', 'Asia/Novosibirsk', 'Asia/Omsk', 'Asia/Oral', 'Asia/Phnom_Penh', 'Asia/Pontianak', 'Asia/Pyongyang', 'Asia/Qatar', 'Asia/Qostanay', 'Asia/Qyzylorda', 'Asia/Riyadh', 'Asia/Sakhalin', 'Asia/Samarkand', 'Asia/Seoul', 'Asia/Shanghai', 'Asia/Singapore', 'Asia/Srednekolymsk', 'Asia/Taipei', 'Asia/Tashkent', 'Asia/Tbilisi', 'Asia/Tehran', 'Asia/Thimphu', 'Asia/Tokyo', 'Asia/Tomsk', 'Asia/Ulaanbaatar', 'Asia/Urumqi', 'Asia/Ust-Nera', 'Asia/Vientiane', 'Asia/Vladivostok', 'Asia/Yakutsk', 'Asia/Yangon', 'Asia/Yekaterinburg', 'Asia/Yerevan', 'Atlantic/Azores', 'Atlantic/Bermuda', 'Atlantic/Canary', 'Atlantic/Cape_Verde', 'Atlantic/Faroe', 'Atlantic/Madeira', 'Atlantic/Reykjavik', 'Atlantic/South_Georgia', 'Atlantic/St_Helena', 'Atlantic/Stanley', 'Australia/Adelaide', 'Australia/Brisbane', 'Australia/Broken_Hill', 'Australia/Darwin', 'Australia/Eucla', 'Australia/Hobart', 'Australia/Lindeman', 'Australia/Lord_Howe', 'Australia/Melbourne', 'Australia/Perth', 'Australia/Sydney', 'Canada/Atlantic', 'Canada/Central', 'Canada/Eastern', 'Canada/Mountain', 'Canada/Newfoundland', 'Canada/Pacific', 'Europe/Amsterdam', 'Europe/Andorra', 'Europe/Astrakhan', 'Europe/Athens', 'Europe/Belgrade', 'Europe/Berlin', 'Europe/Bratislava', 'Europe/Brussels', 'Europe/Bucharest', 'Europe/Budapest', 'Europe/Busingen', 'Europe/Chisinau', 'Europe/Copenhagen', 'Europe/Dublin', 'Europe/Gibraltar', 'Europe/Guernsey', 'Europe/Helsinki', 'Europe/Isle_of_Man', 'Europe/Istanbul', 'Europe/Jersey', 'Europe/Kaliningrad', 'Europe/Kiev', 'Europe/Kirov', 'Europe/Lisbon', 'Europe/Ljubljana', 'Europe/London', 'Europe/Luxembourg', 'Europe/Madrid', 'Europe/Malta', 'Europe/Mariehamn', 'Europe/Minsk', 'Europe/Monaco', 'Europe/Moscow', 'Europe/Oslo', 'Europe/Paris', 'Europe/Podgorica', 'Europe/Prague', 'Europe/Riga', 'Europe/Rome', 'Europe/Samara', 'Europe/San_Marino', 'Europe/Sarajevo', 'Europe/Saratov', 'Europe/Simferopol', 'Europe/Skopje', 'Europe/Sofia', 'Europe/Stockholm', 'Europe/Tallinn', 'Europe/Tirane', 'Europe/Ulyanovsk', 'Europe/Uzhgorod', 'Europe/Vaduz', 'Europe/Vatican', 'Europe/Vienna', 'Europe/Vilnius', 'Europe/Volgograd', 'Europe/Warsaw', 'Europe/Zagreb', 'Europe/Zaporozhye', 'Europe/Zurich', 'GMT', 'Indian/Antananarivo', 'Indian/Chagos', 'Indian/Christmas', 'Indian/Cocos', 'Indian/Comoro', 'Indian/Kerguelen', 'Indian/Mahe', 'Indian/Maldives', 'Indian/Mauritius', 'Indian/Mayotte', 'Indian/Reunion', 'Pacific/Apia', 'Pacific/Auckland', 'Pacific/Bougainville', 'Pacific/Chatham', 'Pacific/Chuuk', 'Pacific/Easter', 'Pacific/Efate', 'Pacific/Enderbury', 'Pacific/Fakaofo', 'Pacific/Fiji', 'Pacific/Funafuti', 'Pacific/Galapagos', 'Pacific/Gambier', 'Pacific/Guadalcanal', 'Pacific/Guam', 'Pacific/Honolulu', 'Pacific/Kiritimati', 'Pacific/Kosrae', 'Pacific/Kwajalein', 'Pacific/Majuro', 'Pacific/Marquesas', 'Pacific/Midway', 'Pacific/Nauru', 'Pacific/Niue', 'Pacific/Norfolk', 'Pacific/Noumea', 'Pacific/Pago_Pago', 'Pacific/Palau', 'Pacific/Pitcairn', 'Pacific/Pohnpei', 'Pacific/Port_Moresby', 'Pacific/Rarotonga', 'Pacific/Saipan', 'Pacific/Tahiti', 'Pacific/Tarawa', 'Pacific/Tongatapu', 'Pacific/Wake', 'Pacific/Wallis', 'US/Alaska', 'US/Arizona', 'US/Central', 'US/Eastern', 'US/Hawaii', 'US/Mountain', 'US/Pacific', 'UTC']

Cast specified columns to JSON array type

Unique identifier (API): cast_json_array

This action marks input columns as "JSONArray" type, implying that the values in the column are expected to be JSON strings parsable into arrays of elements with uniform type.

Cast specified columns to JSON object type

Unique identifier (API): cast_json_object

This action marks input columns as "JSONObject" type, implying that the values in the column are expected to be JSON strings parsable into objects with attributes and values.

Cast columns to numeric type

Unique identifier (API): cast_numeric

Cast the selected columns to numeric type.

Column Group

Get column group components

Unique identifier (API): get_column_group_components

Extracts specified components from a column group by their suffix. If a specified component suffix does not exist, will return a null column as the corresponding output.

Examples:

For the following input table:

dummy_col	__group_grp_a_0	__group_grp_a_1	__group_grp_a_2
aaa	1	2	1
bbb	Sentence A	Sentence B	Sentence C

with: * input column: 'grp_a' * output columns: ['component_0_from_grp_a', 'component_2_from_grp_a'] * suffixes: [0, 2]

The output is:

dummy_col	__group_grp_a_0	__group_grp_a_1	__group_grp_a_2	component_0_from_grp_a	component_2_from_grp_a
aaa	1	2	1	1	1
bbb	Sentence A	Sentence B	Sentence C	Sentence A	Sentence C

Conversion

Convert numeric column to DateTime

Unique identifier (API): numeric_to_datetime

Converts the given numeric columns into UTC timestamps, interpreting them as offset values in the given unit since the specified epoch.

Parameters	Parameter Description	Default	Parameter Type
units	Specifies the unit of time that the values in the numeric column represent.	Seconds	single-select
rel_to	Specifies the "origin" UTC DateTime. The conversion from Numeric to DateTime shall be relative to this "origin".	1970-01-01	string

Examples:

For units == 'seconds', rel_to="1970-01-01T00:00:00" and for the following input table:

numeric timestamps
0
-86400
86400

The expected output is:

numeric timestamps
1970-01-01 00:00:00
1969-12-31 00:00:00
1970-01-02 00:00:00

Custom Formula

Create a new column with a formula

Unique identifier (API): create_new_column_with_formula_v2

Creates a new column with any user-entered formula. Common Excel/SQL functions and operators for manipulating Numeric, DateTime, Text, and Boolean values are permitted. For a full list of allowed functions, see documentation.

Parameters	Parameter Description	Default	Parameter Type
expression	A formula involving column names. For example: 'column_B / column_A + log(column_D * 2)'		formula

Examples:

Consider the dataset:

manufacturer	model	year	displ	cty	hwy
audi	a4 quattro	1999	1.8	16	25
audi	a6 quattro	1999	2.8	15	24
audi	a6 quattro	2008	4.2	16	23
audi	a6 quattro	2008	4.2	16	23
chevrolet	c1500 suburban 2wd	2008	6	12	17
chevrolet	corvette	1999	5.7	16	26
chevrolet	corvette	1999	5.7	15	23
chevrolet	k1500 tahoe 4wd	1999	6.5	14	17
chevrolet	malibu	1999	3.1	18	26
dodge	caravan 2wd	1999	3.8	15	22

where cty, hwy and year are of Numeric type.

Given the formula 'hwy - cty' and the output column name 'hwy_minus_cty', the result will be:

manufacturer	model	year	displ	cty	hwy	hwy_minus_cty
audi	a4 quattro	1999	1.8	16	25	9
audi	a6 quattro	1999	2.8	15	24	9
audi	a6 quattro	2008	4.2	16	23	7
audi	a6 quattro	2008	4.2	16	23	7
chevrolet	c1500 suburban 2wd	2008	6	12	17	5
chevrolet	corvette	1999	5.7	16	26	10
chevrolet	corvette	1999	5.7	15	23	8
chevrolet	k1500 tahoe 4wd	1999	6.5	14	17	3
chevrolet	malibu	1999	3.1	18	26	8
dodge	caravan 2wd	1999	3.8	15	22	7

where the new hwy_minus_cty column will be of Numeric type.

For the same input data, the formula 'IF(year <= 2000, "20th", "21st")' with the output column name 'century' will result in:

manufacturer	model	year	displ	cty	hwy	century
audi	a4 quattro	1999	1.8	16	25	20th
audi	a6 quattro	1999	2.8	15	24	20th
audi	a6 quattro	2008	4.2	16	23	21st
audi	a6 quattro	2008	4.2	16	23	21st
chevrolet	c1500 suburban 2wd	2008	6	12	17	21st
chevrolet	corvette	1999	5.7	16	26	20th
chevrolet	corvette	1999	5.7	15	23	20th
chevrolet	k1500 tahoe 4wd	1999	6.5	14	17	20th
chevrolet	malibu	1999	3.1	18	26	20th
dodge	caravan 2wd	1999	3.8	15	22	20th

where the new century column will be of Text type.

Transform columns with a formula

Unique identifier (API): transform_columns_with_formula_v2

Transforms one or more column(s) with a user-specified formula. Common Excel/SQL functions and operators for manipulating Numeric, DateTime, Text, Boolean and Categorical values as well as JSON Objects/Arrays are permitted. For a full list of these functions, see documentation.

Parameters	Parameter Description	Default	Parameter Type
formula	Formula where $COL/$COLUMN refers to the column(s) to be transformed. For example, "col_A + col_B - $COL"		formula

Examples:

For example, given the following dataset

qty_1	qty_2
10	null
20	15
15	6
null	1
8	8

and formula = ""$col + (qty_2 + $col) * 10"", with input column qty_1, the result will be

qty_1	qty_2
null	null
370	15
225	6
null	1
168	8

Data Cleaning/Processing

Concatenate with delimiter

Unique identifier (API): concat_cols_delim

Concatenates one or more text columns / column groups with a specified delimiter.

Parameters	Parameter Description	Default	Parameter Type	Suggested Values
delimiter	Delimiter to be appended between concatenated values		string	[',', ';', '-', '

Examples:

Assuming delimiter == ', ' and the input table is: (one column, one group with 2 components)

col_1	__group_grp_0	__group_grp_1
Hello	World	A
Hello	null	B

The output will be:

col_1	__group_grp_0	__group_grp_1	comb
Hello	World	A	Hello, World, A
Hello	null	B	Hello, , B

Copy

Unique identifier (API): copy

Creates a copy of the specified columns OR column groups

Examples:

For the following input table: (2 columns and 1 group with 2 components)

col_1	col_2	__group_grp_0	__group_grp_1
0.14667831357791283	2194986032	0.6765007779041725	0.876755413362593
0.8424524032441315	-8793057235	0.21887501332663328	0.6874699121416429
0.08379407453894183	-1300419439	0.2108548261597638	0.39335408485745316
0.09723766918372134	2467206050	0.2499576969146754	0.783647656429706
0.13917572512342702	8415264685	0.9150713227141526	0.071808029978221

We select to copy: [col_2, grp]. (grp is the name of the group). The output is:

col_1	col_2	__group_grp_0	__group_grp_1	col_2_copy	__group_grp_copy_0	__group_grp_copy_1
0.14667831357791283	2194986032	0.6765007779041725	0.876755413362593	2194986032	0.6765007779041725	0.876755413362593
0.8424524032441315	-8793057235	0.21887501332663328	0.6874699121416429	-8793057235	0.21887501332663328	0.6874699121416429
0.08379407453894183	-1300419439	0.2108548261597638	0.39335408485745316	-1300419439	0.2108548261597638	0.39335408485745316
0.09723766918372134	2467206050	0.2499576969146754	0.783647656429706	2467206050	0.2499576969146754	0.783647656429706
0.13917572512342702	8415264685	0.9150713227141526	0.071808029978221	8415264685	0.9150713227141526	0.071808029978221

Drop

Unique identifier (API): drop

Drop columns and/or column groups

Examples:

For example, given the following dataset

col_1	col_2	__group_A_0	__group_A_1	__group_B_mean	__group_B_count
0.666099547109415	cat_1	0.8268666013533758	0	0.9842078018727513	0
0.293068258318807	cat_2	0.9747909820892654	1	0.703905046233064	1
0.2961075040827643	cat_3	0.14699408293664762	2	0.5417745101099833	2
0.11255366333183248	cat_4	0.5392931543191276	3	0.35921739535158015	3
0.9707308564097861	cat_5	0.23839121304354394	4	0.8751438273689285	4

with input column col_1 and column group A, the result is

col_2	__group_B_mean	__group_B_count
cat_1	0.9842078018727513	0
cat_2	0.703905046233064	1
cat_3	0.5417745101099833	2
cat_4	0.35921739535158015	3
cat_5	0.8751438273689285	4

Find and replace

Unique identifier (API): find_replace

Finds and replaces the specified strings in the input column(s) according to the given mapping

Parameters	Parameter Description	Default	Parameter Type
replacements	Mapping between the search strings and their replacements		map
match_case	Whether the matches are case sensitive	False	boolean
positional_match	Matches the search only if a match is found in the specified position in the string	anywhere	single-select

Examples:

For the following input table:

col_x	col_y
They won the fifth place	They won the sixth place
No match here	whole word is th

and input columns: ['col_x', 'col_y'] and parameters:

parameters = {
    'replacements': [['th', '##']],
    'positional_match': 'prefix',
    'match_case': 'True'
}

The output will be:

For ('prefix', True):

col_x	col_y
They won ##e fifth place	They won ##e sixth place
No match here	whole word is ##

Impute categorical columns

Unique identifier (API): impute_categorical

Fill missing values in categorical input columns by a specified method

Parameters	Parameter Description	Default	Parameter Type
method	Method for imputing input columns	most_frequent	single-select
fill_value	Value to fill when method is specified as constant		string

Examples:

For example, a categorical column white, black, None, white, yellow is changed to white, black, white, white, yellow with method = ""most_frequent"" (missing values are filled with the most frequent value "white").

Impute numeric columns

Unique identifier (API): impute_numeric

Fill missing values in numeric input columns by a specified method

Parameters	Parameter Description	Default	Parameter Type
method	Method for imputing input columns	mean	single-select
fill_value	Value to fill when method is specified as constant		float

Examples:

For example, a numeric column 100, 120, None, 80, 100, None is changed to 100, 120, 100, 80, 100, 100 with method = ""mean"" (missing values are filled with the mean 100).

Rename

Unique identifier (API): rename

Renames specified columns OR column groups

Examples:

For the following input table: (3 columns and 1 group with 2 components)

col_1	col_2	col_3	__group_grp_0	__group_grp_1
0.5267075	8.24489e+07	1.23345	0.2635994	0.1005395
0.5707881	2.044422e+09	-0.1124	0.3945866	0.4787061
0.6478216	-5.179201e+09	0.00011	0.1314026	0.2335102

We select to rename: [col_2, grp]. (grp is the name of the group) to [col_A, grp_A]. The output is:

col_1	col_A	col_3	__group_grp_A_0	__group_grp_A_1
0.5267075	8.24489e+07	1.23345	0.2635994	0.1005395
0.5707881	2.044422e+09	-0.1124	0.3945866	0.4787061
0.6478216	-5.179201e+09	0.00011	0.1314026	0.2335102

Sort on columns

Unique identifier (API): sort_on_columns

Sort the dataset based on the input columns in a specified order (ascending/descending)

Examples:

For the following input table:

animal	colour	adopted	qty	desexed	note
cat	yellow	2016-07-31T00:00:12	10	True	There is a story
dog	blue	2017-08-20T07:23:54	4	True
cat	blue	2017-09-20T00:23:50	9	True	No story
cat	blue	2018-05-01T04:40:15	8	True	There is a story
dog	yellow	2019-02-05T12:12:02	1	True	There is another story
dog	black	2019-08-20T07:23:02	2	False	There is a story
cat	black	2020-01-20T00:23:50	2	True	No story
dog	yellow	2020-08-02T15:00:08	2	False

We select sorting by: qty in ascending order and adopted in descending order. The output is:

animal	colour	adopted	qty	desexed	note
dog	yellow	2019-02-05 12:12:02	1	True	There is another story
dog	yellow	2020-08-02 15:00:08	2	False
cat	black	2020-01-20 00:23:50	2	True	No story
dog	black	2019-08-20 07:23:02	2	False	There is a story
dog	blue	2017-08-20 07:23:54	4	True
cat	blue	2018-05-01 04:40:15	8	True	There is a story
cat	blue	2017-09-20 00:23:50	9	True	No story
cat	yellow	2016-07-31 00:00:12	10	True	There is a story

Strip HTML tags

Unique identifier (API): strip_html

Strip HTML tags from text to convert webpages to text documents, sans HTML formatting

Examples:

Strip html tags from text e.g. ""<emp> Text </emp>"" becomes ""Text""

Fill null with the nearest preceding/following values

Unique identifier (API): window_fill_null_by_nearest

Fill null with the nearest non-null values in either preceding or following rows after ordering, optionally with partitioning

Parameters	Parameter Description	Default	Parameter Type
method	Whether to use the nearest value in preceding or following rows	preceding	single-select

Examples:

For example, given the following dataset

animal	colour	adopted	qty
cat	yellow	2020-01-01 00:00:00	null
cat	yellow	2020-01-02 00:00:00	null
cat	yellow	2020-01-03 00:00:00	5.0
cat	yellow	2020-01-04 00:00:00	7.0
cat	yellow	2020-01-05 00:00:00	10.0
cat	white	2020-01-06 00:00:00	1.0
cat	white	2020-01-07 00:00:00	4.0
cat	white	2020-01-08 00:00:00	null
cat	white	2020-01-09 00:00:00	7.0
cat	white	2020-01-10 00:00:00	8.0
dog	yellow	2020-01-11 00:00:00	9.0
dog	yellow	2020-01-12 00:00:00	7.0
dog	yellow	2020-01-13 00:00:00	6.0
dog	yellow	2020-01-14 00:00:00	5.0
dog	yellow	2020-01-15 00:00:00	10.0
dog	white	2020-01-16 00:00:00	6.0
dog	white	2020-01-17 00:00:00	5.0
dog	white	2020-01-18 00:00:00	8.0
dog	white	2020-01-19 00:00:00	null
dog	white	2020-01-20 00:00:00	null

with input column qty, partitioned by animal and colour and ordered by adopted for method preceding, the result is

animal	colour	adopted	qty
cat	white	2020-01-06 00:00:00	1.0
cat	white	2020-01-07 00:00:00	4.0
cat	white	2020-01-08 00:00:00	4.0
cat	white	2020-01-09 00:00:00	7.0
cat	white	2020-01-10 00:00:00	8.0
cat	yellow	2020-01-01 00:00:00	null
cat	yellow	2020-01-02 00:00:00	null
cat	yellow	2020-01-03 00:00:00	5.0
cat	yellow	2020-01-04 00:00:00	7.0
cat	yellow	2020-01-05 00:00:00	10.0
dog	white	2020-01-16 00:00:00	6.0
dog	white	2020-01-17 00:00:00	5.0
dog	white	2020-01-18 00:00:00	8.0
dog	white	2020-01-19 00:00:00	8.0
dog	white	2020-01-20 00:00:00	8.0
dog	yellow	2020-01-11 00:00:00	9.0
dog	yellow	2020-01-12 00:00:00	7.0
dog	yellow	2020-01-13 00:00:00	6.0
dog	yellow	2020-01-14 00:00:00	5.0
dog	yellow	2020-01-15 00:00:00	10.0

Date/DateTime

Add time delta

Unique identifier (API): add_timedelta

Adds a time delta (in seconds) to one or more DateTime columns. Changes the columns in place.

Parameters	Parameter Description	Default	Parameter Type
time_delta_sec	This is the time-duration in seconds to be added to one or more DateTime columns.	0	integer

Examples:

For a time_delta_sec == -100 and the given datetime 2022-06-30 23:51:08.825072 the output is 2022-06-30 23:49:28.825072

Create cyclic encoding features

Unique identifier (API): cyclic_encode

A directive that receives one or more columns that are cyclical in nature. denote the column as 'x' and the period of the column as 'p'. For each such columns, creates 2 new columns which are: x_sin = sin(frac{2\pix}p), x_cos = cos(frac{2\pix}p)

Parameters	Parameter Description	Default	Parameter Type
period	The period of the cyclic feature	1	float

Compute time elapsed

Unique identifier (API): diff_datetime

Calculates the time difference in seconds between two DateTime columns

Parameters	Parameter Description	Default	Parameter Type
units	Specifies the units of the time difference result	Seconds	single-select

Examples:

If the time difference units are Hours, for the following input:

col_1	col_2
2020-07-23 11:26:04.812256	2020-07-31 11:26:04.812256

We expect the following output:

col_1	col_2	diff_1
2020-07-23 11:26:04.812256	2020-07-31 11:26:04.812256	-192.0

Extract AM/PM

Unique identifier (API): extract_am_pm

Extracts the AM/PM value for rows of a DateTime column

Examples:

2020-04-15T15:53:44 -> will return: PM

Extract day of month

Unique identifier (API): extract_day_of_month_number

Extract the day of month as a number from 1 to 31

Examples:

2020-04-15T15:53:44 -> will return: 15

Extract day of week name

Unique identifier (API): extract_day_of_week_name

Extract the name of the day in week ('Monday' to 'Sunday')

Examples:

2020-04-15T15:53:44 -> will return: Wednesday

Extract day of week number

Unique identifier (API): extract_day_of_week_number

Extract the day of week as a number ('Sunday' = 1, 'Saturday' = 7)

Examples:

2020-04-15T15:53:44, Days coding is: Monday=0, Tuesday=1, ..., Sunday=6 -> will return: 02.

Extract hours

Unique identifier (API): extract_hour

Extract hour of the day as a number from 0 to 23

Examples:

2020-04-15T15:53:44 -> will return: 15

Extract minutes

Unique identifier (API): extract_minutes

Extract minutes after the hour as a number from 0 to 59

Examples:

2020-04-15T15:53:44 -> will return: 53

Extract month name

Unique identifier (API): extract_month_name

Extracts the months names of a DateTime column

Examples:

2020-04-15T15:53:44 -> will return: April

Extract month number

Unique identifier (API): extract_month_number

Extract the month as a number from 1 to 12

Extract quarter

Unique identifier (API): extract_quarter

Extract the quarter (3-month period) of the year in which the dates fall as an integer from 1 to 4.

Examples:

2020-04-15T15:53:44 -> will return: 02

Extract seconds

Unique identifier (API): extract_seconds

Extract seconds after the minute as a number from 0 to 59

Examples:

2020-04-15T15:53:44 -> will return: 44

Extract timestamp

Unique identifier (API): extract_timestamp

Extracts the POSIX timestamp. (Number of seconds since January 1st, 1970

Examples:

Returns a POSIX timestamp as float. (Number of seconds since January 1st, 1970).

2020-04-15T15:53:44 -> will return: 1586930024.0.

Extract UTC offset

Unique identifier (API): extract_utc_offset

Extracts the UTC offset (in hours) from timezone aware string timestamps

Parameters	Parameter Description	Default	Parameter Type	Suggested Values
date_format	The date_format of the column	%Y-%m-%dT%H:%M:%S%z	string	['%Y-%m-%dT%H:%M:%S.%fZ%z', '%b %d %Y %H:%M:%S.%f%z', '%Y-%m-%dT%H:%M:%S.%f%z', '%m-%d-%Y %H:%M:%S.%f%z', '%d-%m-%Y %H:%M:%S.%f%z', '%d %b %Y %H:%M:%S.%f%z', '%Y/%m/%d %H:%M:%S.%f%z', '%Y-%m-%d %H:%M:%S.%f%z', '%d/%m/%Y %H:%M:%S.%f%z', '%Y-%m-%dT%H:%M:%SZ%z', '%d%m%Y %H:%M:%S.%f%z', '%m%d%Y %H:%M:%S.%f%z', '%Y%m%d %H:%M:%S.%f%z', '%m-%d-%Y %H:%M:%S%z', '%Y/%m/%d %H:%M:%S%z', '%Y-%m-%dT%H:%M:%S%z', '%b-%d-%Y %H:%M:%S%z', '%d-%m-%Y %H:%M:%S%z', '%d-%b-%Y %H:%M:%S%z', '%d/%m/%Y %H:%M:%S%z', '%Y-%m-%d %H:%M:%S%z', '%m/%d/%Y %I:%M:%S %p%z', '%Y%m%d %H:%M:%S%z', '%Y-%m-%dT%H:%MZ%z', '%m%d%Y %H:%M:%S%z', '%d%m%Y %H:%M:%S%z', '%m-%d-%Y%z', '%Y-%m-%d%z', '%d-%m-%Y%z', '%m %d %Y%z', '%d-%b-%Y%z', '%Y/%m/%d%z', '%d %m %Y%z', '%m/%d/%Y%z', '%Y %m %d%z', '%d/%m/%Y%z', '%d %b %Y%z', '%b-%d-%Y%z', '%b %d %Y%z', '%m%d%Y%z', '%Y%m%d%z', '%d%m%Y%z', '%m%Y%z', '%m %Y%z', '%m-%Y%z', '%m/%Y%z']

Examples:

For a date_format == ”%Y-%m-%dT%H:%M:%S%z” and the following input:

col_1
1970-01-01T23:23:14
2000-10-10T10:14:42-0515
1983-02-03T03:00:51
null
2020-07-06T18:52:36-0000
1948-05-14T02:23:14-0845

The expected output is:

col_1	utc_offset_hrs
1970-01-01T23:23:14	0430
2000-10-10T10:14:42-0515	-5.25
1983-02-03T03:00:51	null
null	null
2020-07-06T18:52:36-0000	0.0
1948-05-14T02:23:14-0845	-8.75

Extract UTC offset from UTC timestamp and timezone

Unique identifier (API): extract_utc_offset_tz

Extracts the offset (in hours) from UTC, given the UTC DateTime column and the timezone name column.

Examples:

For the following input:

datetimes	timezones
1970-01-01 23:23:14	Pacific/Pago_Pago
1990-05-20 09:09:09	Asia/Jerusalem
2000-10-10 10:14:42	pacific/pago_pago
1983-02-03 03:00:51	null
null	Arctic/Longyearbyen
2020-07-06 18:52:36	Mars/Jezero crater

The expected output is:

datetimes	timezones	utc_offset_hrs
1970-01-01 23:23:14	Pacific/Pago_Pago	-11.0
1990-05-20 09:09:09	Asia/Jerusalem	3.0
2000-10-10 10:14:42	pacific/pago_pago	null
1983-02-03 03:00:51	null	null
null	Arctic/Longyearbyen	null
2020-07-06 18:52:36	Mars/Jezero crater	null

The reasons for the null values are:

1. pacific/pago_pago doesn't exist in our timezone list. (Pacific/Pago_Pago does!)
2. null timezone value
3. null datetime value
4. Mars/Jezero crater doesn’t exist in our timezone list. (Maybe in the future)

Extract week number in year

Unique identifier (API): extract_week_number_in_year

Extracts the week number (between 1-52)

Extract year

Unique identifier (API): extract_year

Extract the year as a number

Examples:

2020-04-15T15:53:44 -> will return: 2020

Get daylight savings indicator

Unique identifier (API): get_dst_indicator

Generates a boolean column which indicates if a localized timestamp column (represented by the combination of two columns: A UTC DateTime column and an IANA timezone name column) has an active daylight saving.

Examples:

For the following input:

time_utc	tz_name
2020-08-20 13:13:14	Australia/Melbourne
2020-02-20 13:13:14	Australia/Melbourne
2020-02-20 13:13:14	null
2020-02-20 13:13:14	Mars/Jezero Crater
null	Australia/Melbourne

The expected output is:

time_utc	tz_name	dst_active
2020-08-20 13:13:14	Australia/Melbourne	false
2020-02-20 13:13:14	Australia/Melbourne	true
2020-02-20 13:13:14	null	null
2020-02-20 13:13:14	Mars/Jezero Crater	null
null	Australia/Melbourne	null

And the reasons for the null values are: 1. null timezone value 2. Mars/Jezero crater doesn’t exist in our timezone list. (Maybe in the future) 3. null time_utc

Get local time

Unique identifier (API): get_local_time

Generates local time given the UTC timestamp and the timezone's offset in hours.

Examples:

For the following input: (2 pairs of DateTime | offset, working on both):

datetime_1	offset_1	datetime_2	offset_2
1970-01-01 23:23:14	4.5	null	null
2000-10-10 10:14:42	-5.25	2020-07-06 18:52:36	0.0
1983-02-03 03:00:51	2.75	1948-05-14 02:23:14	null

The expected output is:

datetime_1	offset_1	datetime_2	offset_2	local_time_1	local_time_2
1970-01-01 23:23:14	4.5	null	null	1970-01-02 03:53:14	null
2000-10-10 10:14:42	-5.25	2020-07-06 18:52:36	0.0	2000-10-10 04:59:42	2020-07-06 18:52:36
1983-02-03 03:00:51	2.75	1948-05-14 02:23:14	null	1983-02-03 05:45:51	null

Get timezone name

Unique identifier (API): get_tz_name

Generates a list of timezone names matching the pair of a UTC DateTime column and a numeric UTC offset (in hours) column. The list is in a text format with values separated by a pipe

Examples:

For the following input: (2 pairs of DateTime | offset, working on both)

datetime_1	offset_1	datetime_2	offset_2
1970-01-01 23:23:14	4.5	null	null
2000-10-10 10:14:42	-5.25	2020-07-06 18:52:36	0.0
1983-02-03 03:00:51	2.75	1948-05-14 02:23:14	null

The expected output is:

datetime_1	offset_1	datetime_2	offset_2	timezones_1	timezones_2
1970-01-01 23:23:14	4.5	null	null	Asia/Kabul
2000-10-10 10:14:42	-5.25	2020-07-06 18:52:36	0.0		Africa/Abidjan, Africa/Accra, Africa/Bamako, Africa/Banjul, Africa/Bissau, Africa/Conakry, Africa/Dakar, Africa/Freetown, Africa/Lome, Africa/Monrovia, Africa/Nouakchott, Africa/Ouagadougou, Africa/Sao_Tome, America/Danmarkshavn, America/Scoresbysund, Atlantic/Azores, Atlantic/Reykjavik, Atlantic/St_Helena, GMT, UTC
1983-02-03 03:00:51	2.75	1948-05-14 02:23:14	null

Get timezone name from geolocation

Unique identifier (API): get_tz_name_from_geolocation

Given the geolocation as a pair of numeric columns (latitude and longitude), creates a column of timezone names of each geolocation. Returns null values if no matching timezone is found, or if the geo coordinates are invalid.

Examples:

For the following input:

lat	lng
50.45817318865369	35.99253168643526
30.56916325861897	65.90262778021655
51.936292660801115	26.071955568518586
21.180638320519932	151.80832165098863
67.04735445996346	120.61522491454524
null	97.50232778607207
51.07973011677217	null

The expected output is:

lat	lng	tz_names
50.45817318865369	35.99253168643526	Europe/Moscow
30.56916325861897	65.90262778021655	Asia/Kabul
51.936292660801115	26.071955568518586	Europe/Minsk
21.180638320519932	151.80832165098863	null
67.04735445996346	120.61522491454524	Asia/Yakutsk
null	97.50232778607207	null
51.07973011677217	null	null

Round DateTime

Unique identifier (API): round_datetime

Rounds a DateTime column (in place) to 15, 30 or 60 minutes

Parameters	Parameter Description	Default	Parameter Type
round_to	Time rounding resolution. E.g. for "half_hour", 14:39:11 --> 14:30:00		single-select

Examples:

""2020-04-15T15:53:44"" with rounding to 30 minutes. Output ""2020-04-15T16:00:00""

Feature Engineering

Create features using autoencoder neural network

Unique identifier (API): autoencoder

Create new numerical features by combining existing numerical features in novel and non-linear ways using an autoencoder

Parameters	Parameter Description	Default	Parameter Type	Parameter Min	Parameter Max	Parameter Step
num_epochs	The number of iterations or epochs for optimizing the loss function over the training set.	288	range	1.0	1000.0	1.0
batch_size	The size of a batch of examples used to compute the Stochastic Gradient updates.	128	range	1.0	8192.0	1.0
learning_rate	Learning rates control the size of the update to parameters during each step.	0.001	range	0.001	0.1	0.001
impute	Strategy for imputing missing values	mean	single-select
create_missing_flag	Create a new feature based on missing values	True	boolean
standardize	Standardize, if true, will transform each feature value by subtracting the mean-value and dividing the result by the standard deviation of the feature	True	boolean

Examples:

Using autoencoder to generate novel and non-linear features based on existing numerical features

Bin columns

Unique identifier (API): bin_columns

This action bins one or more numeric columns by specifying the number of equally-spaced bins or a list of bin edges

Parameters	Parameter Description	Default	Parameter Type
method	Whether to use equally-spaced bins or custom cut positions		single-select
n_bins	Number of equally-spaced bins. This creates n equally-spaced bins from the minimum to maximum in the column.		integer
cut_positions	Custom cut positions in ascending order		float-list
output_format	Format for output labels	[lower, upper)	single-select

Examples:

Discretize a numeric column into categorical ranges e.g. given cut positions 100, 150, 200, 30, 110, 180, 220 can be binned as ""x < 100"", ""100 <= x < 150"", ""150 <= x < 200"", ""x >= 200""

Bin columns by quantiles

Unique identifier (API): bin_columns_by_quantiles

This action bins one or more numeric columns by specifying the number of uniformly-split quantiles or a list of custom quantiles.

Parameters	Parameter Description	Default	Parameter Type
method	Whether to use uniformly-split quantiles or custom split quantiles		single-select
n_bins	Number of uniformly-split quantiles. This creates n bins based on uniformly-split quantiles from 0 to 1		integer
split_quantiles	Custom split quantiles in ascending order		float-list
output_format	Format for output labels	[lower, upper)	single-select

Examples:

For example, 20, 50, 80, 100, 200 is converted to ""0 < x < 0.25"", ""0.25 <= x < 0.5"", ""0.5 <= x < 0.75"", , ""0.75 <= x < 1"", ""0.75 <= x < 1"" with split quantiles 0.25, 0.5, 0.75.

Bin DateTime

Unique identifier (API): bin_datetime

Bins a DateTime column (creates 2 new columns) to 15, 30 or 60 minutes

Parameters	Parameter Description	Default	Parameter Type
bin_res	Upper/lower bounds to round towards in order to bin the time. E.g.: 14:53:15 --> [14:45:00, 15:00:00] when using "quarter_hour" resolution		single-select

Examples:

Creates 2 datetime output columns per each input column which ""bin"" the input column between them. The binning resolution is either: hourly, half-hourly, or 15 minutes. Example: ""2020-04-15T15:53:44"" with half-hourly resolution. Output columns: [""2020-04-15T15:30:00"", ""2020-04-15T16:00:00""]

Bin columns such that the target mean follows upward or downward trend

Unique identifier (API): bin_trend

This action bins one or more numeric columns such that the target mean follows an upward or downward trend

Parameters	Parameter Description	Default	Parameter Type
trend	Up or Down Trend		single-select
algo	Algorithm for trend-binning		single-select

Create missing flag

Unique identifier (API): create_missing_flag

For every input column, create a boolean column flagging the locations of missing values.

Extract PCA Components

Unique identifier (API): extract_pca_v2

Create new numerical features by combining existing numerical features in linear ways using PCA

Parameters	Parameter Description	Default	Parameter Type	Parameter Min	Parameter Max	Parameter Step
limit_components_by	Criterion for selecting the principal components. If the minimum explained variance is used, the components that contribute most to the variance are selected until the cumulative variance is not less than the specified ratio of the total variance.	min_explained_variance	single-select
num_components	The number of principal components to extract from the input data		integer
min_explained_variance	This is the threshold on the ratio of explained variance of a component to the total variance. Only the components with highest variance whose cumulative variance is at least the specified ratio of total variance will be selected	0.8	range	0.01	1.0	0.01
numeric_impute_method	Method for imputing Numeric columns	mean	single-select

Apply UMAP

Unique identifier (API): extract_umap_components

Apply the Uniform Manifold Approximation and Projection (UMAP) algorithm to the input columns to achieve dimensionality reduction. This is a manifold-learning algorithm based on neighbours graphs.

Parameters	Parameter Description	Default	Parameter Type	Parameter Min	Parameter Max	Parameter Step
num_output_dimensions	The dimensionality of the space the input will be embedded into.	2	range	1.0	100.0	1.0
seed	The random seed for the UMAP algorithm. By setting it, the output will be reproducible.	2	integer
n_neighbors	This parameter controls how UMAP balances local versus global structure in the data. See documentation of this action in the knowledge hub for details.NOTE: If larger than number of records in the data, this parameter will be truncated to the number of records.	15	range	2.0	100.0	1.0
metric	Defines the metric of the ambient space of the input data.	euclidean	single-select
min_dist	Controls how tightly UMAP is allowed to pack points together. It, quite literally, provides the minimum distance apart that points are allowed to be in the low dimensional representation. This means that low values of the parameter will result in clumpier embeddings. This can be useful if you are interested in clustering, or in finer topological structure. Larger values of the parameter will prevent UMAP from packing point together and will focus instead on the preservation of the broad topological structure instead.	0.1	range	0.0	1.0	0.01

Get vector representation from text

Unique identifier (API): get_feature_vectors_from_text

Obtain numerical features from text using word embedding model

Examples:

Generates a vector representation for the given text column. For the following input table and input field being 'col_text' and output field being 'col_text_vector'

col_text
null
Test 54 45 Test
Dummy sentence
Just some number 00

The output contains text vectors of dimensionality 96:

col_1	__group_col_text_vector__0	__group_col_text_vector__1	.......................	__group_col_text_vector__95
null	0.123	0.786	.......................	0.313
Test 54 45 Test	-0.92	0.576	.......................	0.415
Dummy sentence	0.567	0.321	.......................	-0.28
Just some number 00	0.876	0.476	.......................	-0.47

Create historical summary aggregation features

Unique identifier (API): historical_summ_agg

Create historical summary aggregation features, e.g. the average purchase amount over the last 6 months

Parameters	Parameter Description	Default	Parameter Type
aggregation_functions	Aggregation methods such as "min" or "max" are functions that reduce a column of values to a single value	['sum']	multi-select
timeframe	Time frame for windowing	3	integer

Examples:

For example, creating columns like average spending last 6 months for each customer, by using the date column, monthly spending and customer ID

Label encode categorical columns

Unique identifier (API): label_encode

Encode each input categorical column into a new column of non-negative integers, following either alphabetical (by default) or customised order. Null values and unseen labels (when a finalized recipe fitted by a dataset is applied on new data) remain as null.

Parameters	Parameter Description	Default	Parameter Type
order_of_categories	Category list that determines the order of labels	[]	string-list

Examples:

E.g. convert the Categories ""A"", ""B"", ""C"", NA to their numerical encoding 0, 1, 2, NA

Scale numeric column(s)

Unique identifier (API): scale_numeric_columns

Scale numeric column(s) by a specified method

Parameters	Parameter Description	Default	Parameter Type	Parameter Min	Parameter Max	Parameter Step
method	Method for scaling including: standard (mean removed, scaled to unit variance), min-max (scaled to [0, 1]), max-abs (scaled to [-1, 1]) and robust (median removed, scaled to a specified quantile range)		single-select
lower_quantile	Lower value for quantile range used in scaling	0.25	range	0.0	1.0	0.05
upper_quantile	Upper value for quantile range used in scaling	0.75	range	0.0	1.0	0.05
relative_error	Relative error for approximating median or a specific quantile. Must be no less than 0.001 for computation efficiency	0.001	range	0.001	1.0	0.001

Examples:

For example, a numeric column 100, 120, 80, 10000, 100 is scaled to 0.01, 0.012, 0.008, 1.0, 0.01 with method = ""max_abs"" (all values are divided by the absolute maximum 10000).

Computes weight of Evidence (WOE) encoding for categorical columns

Unique identifier (API): weight_of_evidence

This action converts categorical columns by Weight of Evidence encoding.

Feature Engineering/Text

Get vector representation from text

Unique identifier (API): get_feature_vectors_from_text

Obtain numerical features from text using word embedding model

Examples:

Generates a vector representation for the given text column. For the following input table and input field being 'col_text' and output field being 'col_text_vector'

col_text
null
Test 54 45 Test
Dummy sentence
Just some number 00

The output contains text vectors of dimensionality 96:

col_1	__group_col_text_vector__0	__group_col_text_vector__1	.......................	__group_col_text_vector__95
null	0.123	0.786	.......................	0.313
Test 54 45 Test	-0.92	0.576	.......................	0.415
Dummy sentence	0.567	0.321	.......................	-0.28
Just some number 00	0.876	0.476	.......................	-0.47

Filtering/Custom Formula

Filter rows

Unique identifier (API): filter_rows_with_formula_v2

Filter rows in data by keeping only rows that satisfy the condition specified in user-entered formula. Common Excel/SQL functions and operators for manipulating Numeric, DateTime, Text, and Boolean values are permitted. For a full list of allowed functions, see documentation.

NOTE: The formula entered must evaluate to a boolean value.

Parameters	Parameter Description	Default	Parameter Type
condition	The condition under which the rows are to be kept, entered as a formula.		formula

Examples:

Consider the dataset:

manufacturer	model	year	displ	cty	hwy
audi	a4 quattro	1999	1.8	16	25
audi	a6 quattro	1999	2.8	15	24
audi	a6 quattro	2008	4.2	16	23
audi	a6 quattro	2008	4.2	16	23
chevrolet	c1500 suburban 2wd	2008	6	12	17
chevrolet	corvette	1999	5.7	16	26
chevrolet	corvette	1999	5.7	15	23
chevrolet	k1500 tahoe 4wd	1999	6.5	14	17
chevrolet	malibu	1999	3.1	18	26
dodge	caravan 2wd	1999	3.8	15	22
dodge	caravan 2wd	2008	4	16	23
dodge	durango 4wd	2008	5.7	13	18
dodge	durango 4wd	2008	5.7	13	18
dodge	ram 1500 pickup 4wd	1999	5.9	11	15
ford	expedition 2wd	1999	4.6	11	17
ford	explorer 4wd	1999	5	13	17
ford	f150 pickup 4wd	1999	4.2	14	17
ford	f150 pickup 4wd	1999	4.2	14	17
ford	f150 pickup 4wd	2008	4.6	13	17
ford	mustang	2008	4	17	26

where cty and year are of Numeric type.

Given the formula 'AND(year > 2000, cty >= 15)', the result will be:

manufacturer	model	year	displ	cty	hwy
audi	a6 quattro	2008	4.2	16	23
audi	a6 quattro	2008	4.2	16	23
dodge	caravan 2wd	2008	4	16	23
ford	mustang	2008	4	17	26

Join

Look up aggregated columns from another dataset

Unique identifier (API): aggregate_lookup

Perform aggregation on another dataset and lookup the content of the summarized table

Examples:

Given the primary dataset (n)

grp
1
3
3

and a secondary dataset that we wish to summarize and then perform a lookup of the summarized info:

grp	val	val1	bool1	datetimes
1	0.45	1	True	2023-11-17T13:08:29
1	0.45	2	True	2023-11-17T13:08:29
3	0.45	3	True	2023-11-17T13:08:29
3	0.45	3.5	True	2023-08-03T08:48:29
3	1	3	False	2022-05-29T03:21:59
3	1	4	False	2022-05-29T03:21:59
2	10	5	False	2022-05-29T03:21:59
2	100	6	False	2022-05-29T03:21:59

If we perform the below aggregations on the secondary table and then lookup the results using grp as the lookup key

• mean(val)
• sum(val1)
• all(bool1)
• any(bool)
• approx_count_distinct(val, 0)
• approx_count_distinct(bool1, 0)
• earliest(datetimes, 0)
• latest(datetimes, 0)
• approx_median(val)
• approx_quantile(val)
• count(bool1, "non_null")
• count(bool1, "null)

we get

grp	avg_val	sum_val1	all_bool1	any_bool1	approx_count_distinct_val	approx_count_distinct_bool1	earliest_datetime	latest_datetime	median_val	quantile_val	count_bool1	count_nulls_bool1
1	0.45	3.0	True	True	1	1	2023-11-17 13:08:29	2023-11-17 13:08:29	0.45	0.45	2	0
3	0.725	13.5	False	True	2	2	2022-05-29T03:21:59	2023-08-03T08:48:29	0.45	0.45	4	0
3	0.725	13.5	False	True	2	2	2022-05-29T03:21:59	2023-08-03T08:48:29	0.45	0.45	4	0

Join

Unique identifier (API): join

Joins another dataset to current dataset given one or more join keys, retaining the specified selection of columns from both datasets.

Parameters	Parameter Description	Default	Parameter Type
join_type	Specifies how to perform the join.		single-select

Examples:

For the following input tables:

Primary Table: (2 regular columns, 1 column group with 2 components)

Id	Name	__group_Like_0	__group_Like_1
1	Patrick	Apple	Milk
2	Albert	Pear	Cheese
3	Maria	Watermelon	Chickpeas
4	Darwin	Grapes	Beans
5	Elizabeth	Kiwi	Softpeas

Secondary Table: (2 regular columns)

User_ID	Like
3	Stars
1	Climbing
1	Code
6	Rugby
4	Apples

We perform a Join with the following parameters:

• join_keys == 'Id' key for primary table.
• join_keys == 'User_ID' key for secondary table
• selected_columns == ['Id', 'Name', 'Like'] for the primary table.
• selected_columns == 'Like' for the secondary table
• join_type == 'Outer Join'

the expected output is:

Id	Name	__group_primary_Like_0	__group_primary_Like_1	secondary_Like
null	null	null	null	Rugby
5	Elizabeth	Kiwi	Softpeas	null
1	Patrick	Apple	Milk	Climbing
1	Patrick	Apple	Milk	Code
3	Maria	Watermelon	Chickpeas	Stars
2	Albert	Pear	Cheese	null
4	Darwin	Grapes	Beans	Apples

Notice that in this example the user performed an Outer Join but have opted to to drop the User_Id column of the secondary table. This resulted in the first row of the output to show an Id of null (User_Id == 6 for that row. This value did not exist in the primary table) and thus the result for this row isn't very usable.

Look up columns from another dataset

Unique identifier (API): lookup_from

Creates specified columns by looking them up from a second dataset. Results in null values if there is no match in the second dataset for a particular record.

Parameters	Parameter Description	Default	Parameter Type
multi_match_resolution	What row to use in case of multiple rows are matching in the other dataset under the specified criteria.	last	single-select

Examples:

For the following input tables:

Primary Table: (2 regular columns, 1 column group with 2 components)

Id	Name	__group_Like_0	__group_Like_1
1	Patrick	Apple	Milk
2	Albert	Pear	Cheese
3	Maria	Watermelon	Chickpeas
4	Darwin	Grapes	Beans
5	Elizabeth	Kiwi	Softpeas

Secondary Table: (2 regular columns)

User_ID	Like
3	Stars
1	Climbing
1	Code
6	Rugby
4	Apples

We perform a Lookup with the following parameters:

• join_criteria == 'Id' key for primary table.
• join_criteria == 'User_ID' key for secondary table
• lookup == 'Like' for the secondary table
• order_secondary_by == 'Like' for the secondary table
• Name prefixes for the current and other datasets as ['left_', 'right_']

the expected output is:

Id	Name	__group_left_Like_0	__group_left_Like_1	right_Like
null	null	null	null	Rugby
5	Elizabeth	Kiwi	Softpeas	null
1	Patrick	Apple	Milk	Climbing
3	Maria	Watermelon	Chickpeas	Stars
2	Albert	Pear	Cheese	null
4	Darwin	Grapes	Beans	Apples

Note: that Lookup only returns one match per row in the primary table. Hence, if there are multiple matches in the secondary table, only the first one will be returned. Hence, it's important to choose a order_secondary_by column in the secondary table.

Outlier Detection

Get anomaly score of records

Unique identifier (API): get_anomaly_scores_from_records

Computes the anomaly score of input data using unsupervised learning. Anomaly score of a record is computed as a measure of the information lost when the record is subject to dimensionality reduction by PCA.

Parameters	Parameter Description	Default	Parameter Type	Parameter Min	Parameter Max	Parameter Step
method	The method used for anomaly detection such as dimensionality reduction using PCA.	pca	single-select
pca_limit_components_by	Criterion for selecting the principal components. If the minimum explained variance is used, the components that contribute most to the variance are selected until the cumulative variance is not less than the specified ratio of the total variance.	specified_explained_var	single-select
pca_num_of_components	The number of principal components to extract from the input data		integer
pca_minimum_explained_variance_needed	This is the threshold on the ratio of cumulative explained variance of the selected principal components to the total variance. Only the components with highest variance whose cumulative variance is at least the specified ratio of total variance will be selected	0.8	range	0.01	1.0	0.01
isolation_forest_num_estimators	The number of trees in the ensemble.	100	integer
max_samples_specification_method	Specifies how max samples for isolation forest is set. Fractional implies that the maximum sample size is a fraction relative to the total number of rows.Count implies that the specified number is treated as an absolute count.	count	single-select
isolation_forest_max_samples_fraction	The fraction of the total number of samples used to train each tree.	1.0	range	0.01	1.0	0.01
isolation_forest_max_samples_count	The number of samples used to train each tree.The value should be greater than 1.	256	integer
seed	Positive integer. Random seed for isolation forest algorithm. Used for obtaining repeatable results.	1	integer

Indicate anomalous values

Unique identifier (API): indicate_anomalous_values

Creates boolean flag columns indicating outliers in the corresponding input columns, using interpercentile range method.E.g. denoting Q1 as the 1st quartile, Q3 as the 3rd quartile and IQR = Q3 - Q1, values smaller than Q1 - 1.5*IQR or larger than Q3 + 1.5IQR will be flagged as outliers.

Parameters	Parameter Description	Default	Parameter Type	Parameter Min	Parameter Max	Parameter Step
decision_mode	How the decision is made for declaring an anomaly: For "Upper threshold": only values larger than the upper threshold will be declared as anomalies. For "Lower threshold": only values lower than the lower threshold will be declared as anomalies. For "Lower and upper threshold": values below the lower threshold and above the upper threshold will be declared as anomalies.	lower_and_upper_thresholds	single-select
low_percentile	The percentile P1 such that all values smaller than P1-alpha*IPR will be flagged as outliers By default, P1 equals Q1 (first quartile).	25	range	0.0	100.0	1.0
high_percentile	The percentile P2 such that all values larger than P2+alpha*IPR will be flagged as outliers By default, P2 equals Q3 (3rd quartile).	75	range	0.0	100.0	1.0
ipr_factor	The interpercentile range (denoted as IPR) equals P2-P1, where P2 and P1 are correspondingly the high and low percentiles. The factor is defined as 'alpha' in the formulas: P1 - alpha * IPR and P2 + alpha * IPR which determine the low and high thresholds for outliers detections. Values above/below these thresholds will be flagged as outliers	1.5	float

Reshaping

Reshape dataset into a pivot table

Unique identifier (API): pivot_v2

Creates a pivot table with specified group-by (index) columns and pivot column. Column groups with equal number of components (one per pivoted value) are created, one per specified column/aggregation function pair.

Parameters	Parameter Description	Default	Parameter Type
pivot_values	List of values that are converted to columns in the output	[]	string-list

Examples:

For example, given the following dataset

animal	size	qty	desexed
cat	small	95	True
cat	large	39	False
cat	small	50	False
cat	large	37	False
cat	small	12	True
dog	large	84	False
dog	small	71	True
dog	large	87	True
dog	small	7	True
dog	large	42	False

with input pivot column size, grouped by column animal, values column qty with aggregation function sum and value column desexed with aggregation function all, the result is

animal	__group_qty_sum_small	__group_qty_sum_large	__group_desexed_all_small	__group_desexed_all_large
dog	78	213	True	False
cat	157	76	False	False

Unpack/Explode JSON arrays into rows

Unique identifier (API): unpack_v2

Reshape a dataset by unpacking one or more JSON array columns and add a new column for position index

Parameters	Parameter Description	Default	Parameter Type
allow_single_quotes	Whether to allow single quoted JSONs	False	boolean

Examples:

For example, given the following dataset

paper_id	author	affilliation
1	["Sara", "Tom", "Max"]	["rmit", "monash", "rmit"]
2	["Sara", "Max", "Lee"]	["rmit", "rmit"]

with input columns ""author"" and ""affilliation"", output column ""position"" for position index, the result is

paper_id	author	affilliation	position
1	Sara	rmit	0
1	Tom	monash	1
1	Max	rmit	2
2	Sara	rmit	0
2	Max	rmit	1
2	Lee	null	2

Unpivot a dataset from wide to long format

Unique identifier (API): unpivot

Unpivot a dataset from wide to long format. Input columns are converted to a single keys column and one or more values column(s) in the output. Columns other than input columns remain.

Text

Change text case

Unique identifier (API): change_text_case

Changes text columns to selected text case (uppercase, lowercase, etc.)

Parameters	Parameter Description	Default	Parameter Type
case	The required change in case of values where "Upper" converts all to uppercase, "Lower" converts all to lowercase and "Capitalize" converts first character to uppercase and remaining to lowercase.		single-select

Examples:

For case == 'upper' and the following input table:

col_1
null
a brown fox quick jumps over the lazy dog
1234
Hello World

The expected output is:

col_1
null
A BROWN FOX QUICK JUMPS OVER THE LAZY DOG
1234
HELLO WORLD

Decode base64-encoded text

Unique identifier (API): decode_base64

Every input column is assumed to be encoded using Base64-encoding. Decode it into thecharacter set (e.g. UTF8) specified. Store the decoded values in the output column

Parameters	Parameter Description	Default	Parameter Type
charset	The encoding to convert the decoded bytes to. This is the charset encoding of the original strings before they were base64 encoded	UTF8	single-select

Extract all matches of a regular expression pattern

Unique identifier (API): extract_all_regex_matches

For each input column, creates a new column of JSONArray type containing all the sub-strings in the corresponding input matching the specified regular expression.

Parameters	Parameter Description	Default	Parameter Type
pattern	The pattern to be found, as a Perl-Compatible Regular Expression. Note that at present, only whole matches are extracted and capturing groups are not supported.		string

Examples:

Given the following input dataset with column types as indicated:

('Description', 'Text')
The IP address of the router: 192.168.1.1
Go to 201 Mills St or call (404) 456-0861
The final amount of the transaction is 25,382.55
Spend $100 or more to enter a draw sponsored by hotels123.com to win a $20,500.00
There is no match 456here

Applying the action with

• Input columns = ['Description']
• Output columns = ['Matches']
• Parameters = {"pattern": r"\b\d+\b(?!$)"}

produces the following dataset with the column types as indicated:

('Description', 'Text')	('Matches', 'JSONArray')
The IP address of the router: 192.168.1.1	["192","168","1"]
Go to 201 Mills St or call (404) 456-0861	["201","404","456"]
The final amount of the transaction is 25,382.55	["25","382"]
Spend $100 or more to enter a draw sponsored by hotels123.com to win a $20,500.00	["100","20","500"]
There is no match 456here	[]

Extract capture groups

Unique identifier (API): extract_capture_groups_to_multicol

Extracts one or more regex capture groups into multiple columns.

Parameters	Parameter Description	Default	Parameter Type
pattern	A regular expression pattern containing one or more capture groups		string

Examples:

For the following pattern == '([aeiou].)([aeiou].)' , the following input table:

col_1	col_2
New_York	bc
Lisbon	df
Tokyo	gh
Paris	jk
null	lm
Munich	np

And assuming we apply the action to column (col_1), with output columns [out_a, out_b], the expected output is:

col_1	col_2	out_a	out_b
New_York	bc	null	null
Lisbon	df	null	null
Tokyo	gh	null	null
Paris	jk	ar	is
null	lm	null	null
Munich	np	un	ic

Get named entities from text

Unique identifier (API): get_named_entities_from_text

Extracts named entities mentioned in input text column into the corresponding output text column as JSON string. The JSON string represents a dictionary-mapping between entity types to the coresponding list of entity mentions in the text.

Examples:

Extracts named entities from a given text column. See the following illustration for an input table with a single text column named col_text and an input field col_text and output field col_entities_from_text:

col_text
Bill Gates addressed Microsoft.
Jack drove to Washington.
Decisions are driven by data.
null
---------------------------------

The output dataframe contains the text column col_entities_from_text:

col_text	col_entities_from_text
Bill Gates addressed Microsoft.	{"People": ["Bill Gates"], "Organizations": ["Microsoft"]}
Jack drove to Washington.	{"People": ["Jack"], "Places": ["Washington"]}
Decisions are driven by data.	{}
	{}
null	{}

Get sentiment scores from text

Unique identifier (API): get_sentiment_scores_from_text

Analyses the sentiment expressed in the input text columns and creates 3 output columns that reflect the strength (a number between 0 to 1, where 1 is the strongest) of positive, neutral and negative sentiment expressed in input text.

Examples:

Extracts sentiment polarity scores from a given text column into three numeric output columns. See the following illustration for an input table and with a single text column named 'tweets' and input field being 'col_text' and output fields being 'positive_sentiment', 'neutral_sentiment' and 'negative_sentiment':

tweets
Thanks for the follow-up. That's why you're the best!
It was a horrible experience, something I would never recommend
''
null
---------------------------------------------------------------

The output dataframe contains the text columns 'positive_sentiment', 'neutral_sentiment' and 'negative_sentiment':

tweets	positive_sentiment	neutral_sentiment	negative_sentiment
Thanks for the follow-up. That's why you're the best!	0.514	0.486	0.0
It was a horrible experience, something I would never recommend	0.0	0.588	0.412
''	0.0	0.0	0.0
null	0.0	0.0	0.0
---------------------------------------------------------------	------------------	-----------------	------------------

Normalize text content

Unique identifier (API): normalize_text

Normalizes a text column by performing one or more normalization steps such as removing non text content or stemming the words.

Parameters	Parameter Description	Default	Parameter Type
steps_to_execute	The required normalization steps	['remove_html_tags', 'replace_urls']	sequence-select

Examples:

If the input data is:

col_1
@JetBlue I was not allowed to drop my bag off for my flight that I was running 10min Late Flight for. Turns out it left over an hour Late Flight.
@VirginAmerica momma I made it! 😁😁😁
@JetBlue bag just delivered and items have been stolen. At first look - my Kate spade bag and bottle of spiced rum!!!
@united then why have I not received my call back its been 3 days...it's an infant car seat how am I supposed to go anywhere with my child
@JetBlue just a heads up, this page no longer exists http://t.co/NsJWVTTjGo
@AmericanAir Thank you, you too!
“@JetBlue: Our fleet's on fleek. http://t.co/vw2v8GVnGq” 😐😑
@AmericanAir yes I did but no response.
@SouthwestAir when is the next flight with free cocktails ?
@AmericanAir have been waiting for a call back for over two hours jan to die tomorrow at 6am Cancelled Flightled what are my options?
@AmericanAir that doesn't really answer my question. Maybe if I provide more details you can give me clarification...
@JetBlue where is our luggage?? Flight 424 has been waiting an hour for bags! #jetblue #poorservice
@united will I make it with the delay?
@SouthwestAir why can't you take me to Knoxville?? yall are my fav #help

applying the action on col_1 with only the remove_stopwords and lemmatize_words stages disabled will result in:

col_1
mention i was not allowed to drop my bag off for my flight that i was running 10min late flight for turns out it left over an hour late flight
mention momma i made it emoji emoji emoji
mention bag just delivered and items have been stolen at first look my kate spade bag and bottle of spiced rum
mention then why have i not received my call back its been number daysit s an infant car seat how am i supposed to go anywhere with my child
mention just a heads up this page no longer exists url
mention thank you you too
mention our fleet s on fleek url emoji emoji
mention yes i did but no response
mention when is the next flight with free cocktails
mention have been waiting for a call back for over two hours jan to die tomorrow at 6am cancelled flightled what are my options
mention that doesn t really answer my question maybe if i provide more details you can give me clarification
mention where is our luggage flight number has been waiting an hour for bags hashtag hashtag
mention will i make it with the delay
mention why can t you take me to knoxville yall are my fav hashtag

For the same data, if all stages including remove_stopwords and lemmatize_words are enabled, the result will be:

col_1
mention allow drop bag flight running 10min late flight turn left hour late flight
mention momma emoji emoji emoji
mention bag deliver item steal look kate spade bag bottle spice rum
mention receive number day infant car seat suppose child
mention head page long exist url
mention thank
mention fleet fleek url emoji emoji
mention yes response
mention flight free cocktail
mention wait hour jan die tomorrow 6 cancel flightled option
mention answer question maybe provide detail clarification
mention luggage flight number wait hour bag hashtag hashtag
mention delay
mention knoxville y fav hashtag

Create flag for regex match

Unique identifier (API): regex_match

Creates an output boolean column which is the result of matching the input column against a regex pattern

Parameters	Parameter Description	Default	Parameter Type
pattern	A regular expression pattern for the matching		string

Examples:

For the following table:

col_1
null
That's my phone number: 054-555-111
Monday is the 1st day of the week
This should match 0490-000-111
This should not match 0490-abc-def

we apply the regex pattern: '0490-\d{3}-\d{3}' Resulting in the following output:

col_1	col_1_regex_match
null	null
That's my phone number: 054-555-111	false
Monday is the 1st day of the week	false
This should match 0490-000-111	true
This should not match 0490-abc-def	false

Substitute using regex

Unique identifier (API): regex_sub

Finds and replaces string patterns in a column via Regex. Can optionally specify additional pattern to filter by, in which case the scope of the find/replace will be restricted to records that match the specified pattern in the column.

Parameters	Parameter Description	Default	Parameter Type
match_pattern	Specify this to restrict the scope of replacement	.*	string
sub_pattern	The pattern that needs to be substituted		string
sub_string	String used to replace values in places where sub_pattern matched the values.		string

Examples:

For the following set of parameters:

match_pattern = '0490-\d{3}-\d{3}'
sub_pattern = '054-\d{3}-\d{3}'
sub_string = 'I WAS REPLACED'

and the following input table:

col_1
null
That's my phone number: 054-555-111
Monday is the 1st day of the week
This should be substituted: 0490-000-111, 054-555-111
This should not be substituted: 0490-000-111, 054-abc-cdf

The output is:

col_1
null
That's my phone number: 054-555-111
Monday is the 1st day of the week
This should be substituted: 0490-000-111, I WAS REPLACED
This should not be substituted: 0490-000-111, 054-abc-cdf

Remove prefix

Unique identifier (API): remove_prefix

Removes a string prefix from the rows of a chosen string column, if the string is found.

Parameters	Parameter Description	Default	Parameter Type
prefix_string	The prefix string to be stripped		string

Examples:

For the prefix_string == 'prefix_' and the following input table:

col_1
null
prefix_to remove
other_not to remove

The expected output is:

col_1
null
to remove
other_not to remove

Remove punctuation

Unique identifier (API): remove_punctuation

Removes the punctuation from the rows of a chosen string column. The punctuation symbols to remove are given by the user as a string

Parameters	Parameter Description	Default	Parameter Type	Suggested Values
punc_symbols	The punctuation symbols to strip	!"#$%&'()*+,-./:;<=>?@[]^_{|}~ | string | ['!"#$%&\'()*+,-./:;<=>?@[\\]^_{	}~']

Examples:

For the following input table:

col_1
null
This. Sentence! Has? Lots of, umm.. punctuation<>
Monday is the 1st day of the week
abc.edf@gmail.com

The output with stripped punctuation is:

col_1
null
This Sentence Has Lots of umm punctuation
Monday is the 1st day of the week
abcedfgmailcom

Remove suffix

Unique identifier (API): remove_suffix

Removes a string suffix from the rows of a chosen string column, if the string is found.

Parameters	Parameter Description	Default	Parameter Type
suffix_string	The suffix string to strip		string

Examples:

For the suffix_string == '_suffix' and the following input table:

col_1
null
remove_suffix
other_not to remove

The expected output is:

col_1
null
remove
other_not to remove

Split on delimiter to multiple columns

Unique identifier (API): split_on_delimiter_to_multicol

For a given column, extracts text components between the delimiters into multiple columns. The delimiter can be one or more specific characters, or a regex pattern to match. The number of splits is determined by the number of provided output column names. Trailing null values are created for records containing fewer components.

Parameters	Parameter Description	Default	Parameter Type	Suggested Values
delimiter	Delimiter to be used for splitting the string		string	[',', ';', '-', '
use_regex	Boolean flag indicating whether the specified delimiter should be treated as a regex pattern or an exact match.	False	boolean
include_tail	Boolean flag indicating wether the remainder (tail) of the string after the final split must be included in the last column	True	boolean

Examples:

For the following set of parameters:

parameters = {'delimiter': ':', 'use_regex': False, 'include_tail': True}

and output columns 'split_0', 'split_1', 'split_2', and the following input table:

col_1
one:two:three:four
no_split

The expected output is:

col_1	split_0	split_1	split_2
one:two:three:four	one	two	three:four
no_split	no_split	null	null

Whereas for include_tail = False, the expected output is:

col_1	split_0	split_1	split_2
one:two:three:four	one	two	three
no_split	no_split	null	null

Split to array using a delimiter or pattern

Unique identifier (API): split_to_array

Obtain array (JSON) columns resulting from splitting the selected text columns using a delimiter or a regex pattern. The number of splits can be specified to be a certain limit, otherwise the splitting of the text is indefinite until no more occurrences of the delimiter are found.

Parameters	Parameter Description	Default	Parameter Type	Suggested Values
delimiter	The delimiter to be used for splitting the string.		string	[',', ';', '-', '
use_regex	To split by a regex pattern, set this value to "True".	False	boolean
limit_splits	Set this to "True" and set the number of splits to the desired number, if not intending to split indefinitely.	False	boolean
max_number_of_splits	How many splits to obtain at most, if the splits are being limited.	2	integer

Examples:

Given the following input dataset with column types as indicated:

('col_1', 'Text')	('col_2', 'Numeric')	('col_3', 'Text')
1,2,3,4,5	1	a
a,b,c,d	3.14159	d
e,f,g	2.23606	x
one, two , three	1.732	no_split
no_split	nan

with:

• Input columns as ['col_1', 'col_3'],
• Parameters delimiter=',', use_regex=False, limit_splits=True, max_number_of_splits=3,

the result will be the following dataset with column types as indicated:

('col_1', 'JSONArray')	('col_2', 'Numeric')	('col_3', 'JSONArray')
["1","2","3,4,5"]	1	["a
["a","b","c,d"]	3.14159	["d
["e","f","g"]	2.23606	["x
["one"," two "," three"]	1.732	["no_split"]
["no_split"]	nan

Strip whitespaces

Unique identifier (API): strip_whitespaces

Strips whitespaces from rows of chosen Text columns.

Parameters	Parameter Description	Default	Parameter Type
mode	The method for stripping the whitespaces. Stripping can be done at the beginning, end, both of the strings, and can even strip ALL of the whitespaces.		single-select

Examples:

For the following table:

+-----------------------+
|col_1                  |
+-----------------------+
| start_whitespace      |
|end_whitespace         |
| start_end_whitespaces |
|test      tab          |
+-----------------------+

and mode == 'End', the expected output is:

+----------------------+
|col_1                 |
+----------------------+
| start_whitespace     |
|end_whitespace        |
| start_end_whitespaces|
|test      tab         |
+----------------------+

Text/Data Cleaning

Map to Text

Unique identifier (API): map_to_string

Replace values of input column(s) using a given mapping.

Parameters	Parameter Description	Default	Parameter Type
mapping	Value that will be replaced and the replacement.		map

Examples:

Assume we have the following single column table:

col_1
dog
cat
null
horse
dogdog

and the following mapping:

mapping = {
    'dog': 'doggy',
    'cat': 'kitten'
}

After running the action, the expected output is:

col_1
doggy
kitten
null
horse
dogdog

where dog -> doggy, cat -> kitten and the rest of the values were unchanged since they didn't appear in the mapping dictionary.

Parse strings as null

Unique identifier (API): parse_strings_as_null

Parses the specified strings in given column(s) as null values

Parameters	Parameter Description	Default	Parameter Type
strings_to_parse	The list of strings that will be parsed as null		string-list

Examples:

For the following input dataset:

col_x	col_y
11	n/a
45	38
null	31
25	1.#QNAN
49	20

where both columns are of text type, applying the action with the parameter strings_to_parse = ['n/a', '', 'NA', 'N/A', '<NA>', 'NULL', 'nan', '1.#IND', '#NA', '-1.#QNAN', '-nan', 'NaN', '1.#QNAN', '-1.#IND', '-NaN', 'null', '#N/A', '#N/A N/A'] and both columns selected in the input will give the following result:

col_x	col_y
11
45	38
	31
25
49	20

Text/JSON

Extract from JSON by path

Unique identifier (API): extract_from_json_by_path

Extract from JSON by the path. Will return null if the path isn't found

Parameters	Parameter Description	Default	Parameter Type
path	The path of the value to extract		string

Get occurrence-count features

Unique identifier (API): get_occurrence_count_features

Count the occurrence for each element of a JSON array. This achieves the same functionality as CountVectorizer in Scikit-learn package.

Examples:

For example, given the following dataset

customer_id	events_A	events_B	events_C
1	["login", "logout"]	["open", "close"]	["log in", "log out"]
2	["login", "home", "logout"]	["open", "idle", "close"]	["log in", "$home$", "log out"]
3	["login", "home", "logout", "login", "logout"]	["open", "idle", "close", "open", "close"]	["log in", "home", "log out", "log in", "log out"]
4	["login", "home", "app", "home", "logout"]	["open", "idle", "active", "idle", "close"]	["log in", "$home$", "app", "home", "log out"]
5		{"open": 0, "close": 1}	["log in", "$home$", "app", "home", "log out"]

with inputs as events_A, events_B and events_C, outputs as A, B and C, the result is

customer_id	events_A	events_B	events_C	__group_A_logout	__group_A_login	__group_A_home	__group_A_app	__group_B_open	__group_B_close	__group_B_idle	__group_B_active	__group_C_log00out	__group_C_log00in	__group_C_00home00	__group_C_home	__group_C_app	__group_C_00app00
1	["login", "logout"]	["open", "close"]	["log in", "log out"]	1	1	0	0	1	1	0	0	1	1	0	0	0	0
2	["login", "home", "logout"]	["open", "idle", "close"]	["log in", "$home$", "log out"]	1	1	1	0	1	1	1	0	1	1	1	0	0	0
3	["login", "home", "logout", "login", "logout"]	["open", "idle", "close", "open", "close"]	["log in", "home", "log out", "log in", "log out"]	2	2	1	0	2	2	1	0	2	2	0	1	0	0
4	["login", "home", "app", "home", "logout"]	["open", "idle", "active", "idle", "close"]	["log in", "$home$", "app", "home", "log out"]	1	1	2	1	1	1	2	1	1	1	1	1	1	0
5		{"open": 0, "close": 1}	["log in", "$home$", "app", "home", "log out"]	0	0	0	0	0	0	0	0	1	1	1	1	0	1

Here in input column events_C, there are white spaces and special characters such as $ and _ in the elements of JSON arrays. These characters other than numbers or letters are replaced with 00 in the output columns.

Parse JSON objects in column

Unique identifier (API): parse_json_objects

Parses each value in the input column as a JSON object, whose schema is specified by the user. Each attribute is extracted as one output column. All output columns will contain null values wherever the Text string is not parsable as a JSON object. If parsable but one or more of the specified attributes is missing or of incompatible type, their corresponding output columns will contain null values.

Parameters	Parameter Description	Default	Parameter Type
allow_single_quotes	Whether to allow single quoted JSONs	False	boolean

Examples:

For the input dataset with column names and data types as indicated below:

movie_id	title	movie_cast
1	Avatar	{"character": "Neytiri", "gender": 1, "name": "Zoe Saldana"}
2	Pirates of...	{"character": "Will T", "gender": 2, "name": "Orlando Bloom"}
3	Spectre	{"character": "Blofeld", "gender": 2, "name": "C Waltz"}
4	Spider-Man 3	{"character": "MJ", "gender": 1, "name": "Kirsten Dunst"}
5	X-Men
6	Avengers	{"broken_json": "3",
7	Captain America	{"character": "Nick Fury", "gender": "M", "name_missing": true}
8	The Hobbit	["Array instead of object 1", "Array instead of object 2"]

Applying this action with input column = 'movie_cast' and attribute/data type/output column trios [("character", "string", "character_name"), ("gender", "integer", "character_gender"), ("name", "string", "character_name")] produces the output:

movie_id	title	movie_cast	character_name	character_gender	character_acted_by
1	Avatar	{"character": "Neytiri", "gender": 1, "name": "Zoe Saldana"}	Neytiri	1	Zoe Saldana
2	Pirates of...	{"character": "Will T", "gender": 2, "name": "Orlando Bloom"}	Will T	2	Orlando Bloom
3	Spectre	{"character": "Blofeld", "gender": 2, "name": "C Waltz"}	Blofeld	2	C Waltz
4	Spider-Man 3	{"character": "MJ", "gender": 1, "name": "Kirsten Dunst"}	MJ	1	Kirsten Dunst
5	X-Men			nan
6	Avengers	{"broken_json": "3",		nan
7	Captain America	{"character": "Nick Fury", "gender": "M", "name_missing": true}	Nick Fury	nan
8	The Hobbit	["Array instead of object 1", "Array instead of object 2"]		nan

Text/NLP

Get topic distribution probabilities from text

Unique identifier (API): get_topic_distribution_from_text

Analyses the input text columns and extracts underlying topics in the text. Each record is considered a text document that has originated from the underlying topics. The output numeric column group contains the probability distribution over the identified topics for each input record (document)

Parameters	Parameter Description	Default	Parameter Type	Parameter Min	Parameter Max	Parameter Step
num_topics	The number of different topics that are assumed to be present in the input text column.	5	range	2.0	500.0	1.0
max_iterations	Sets the maximum number of iterations that can be run by the algorithm for extracting the underlying topics	20	range	15.0	30.0	1.0
seed	Random seed for topic modeling algorithms. Used for obtaining repeatable results.		integer

Examples:

For the following input:

text
you ever wanted to try some yourself
not only a fun read but also a pretty infographic
something not understandable by humans
text of all jane austens works if
not only a fun read but also a pretty infographic

and the parameter values: num_topics == 2 (Number of topics to be extracted)

We get the following output:

text	__group_text_topics_0	__group_text_topics_1
you ever wanted to try some yourself	0.400954	0.599046
not only a fun read but also a pretty infographic	0.239050	0.760950
something not understandable by humans	0.211497	0.788503
text of all jane austens works if	0.435378	0.564622
not only a fun read but also a pretty infographic	0.434958	0.565042

Get TF-IDF features from text

Unique identifier (API): tf_idf

Applies TF-IDF + dimensionality reduction

Parameters	Parameter Description	Default	Parameter Type	Parameter Min	Parameter Max	Parameter Step
post_processing_method	Algorithm for reducing the dimensionality of the TF-IDF output	truncated_svd	single-select
num_output_dimensions	The number of dimensions after post processing of TF-IDF output. For example, if the post-processing method is truncated SVD, and 100 output dimensions are chosen, then the dimensions retained after SVD truncation correspond to 100 largest singular values.	50	range	2.0	100.0	1.0
hashing_tf_num_features_method	How the number of features produced by the Hashing TF stage is determined. The larger the vocabulary size of the corpus, the more the number of hashing features needed for fidelity. If set to 'Automatic', the number of features to be produced by the Hashing TF stage is set to the smallest power of 2 that is closest to the estimated vocabulary size.	auto	single-select
hashing_tf_num_features	The cardinality of the set of possible hash values output by the hash function. The hash function is used to convert a document (text string) into sparse vectors after tokenization. This can be used to control the number of dimensions output by TF-IDF before dimensionality reduction. Smaller numbers result in higher chance of two terms being confused, while larger numbers result in longer computation times. It is advisable to use a power of two for the parameter, otherwise the features will not be mapped evenly to the columns.	4096	range	32.0	65536.0	32.0

Examples:

For the following input:

x
cant say for sure what they used but
there is an r library that includes the
text of all jane austens works if
you ever wanted to try some yourself
they probably got the idea
from
which uses jane austens work extensively
as an ongoing example when
showcasing the tidytext library
the specific library of jane
austens work is library
im guessing they used tidytext
and janeaustenr in r, then used a slightly
different analysis
using the results from r
in tableau or another data-viz
heavy program to make it palatable for
readers
pun intended sorry who has already
done a lot of
really cool analysis like this
they claimed in the text that
they used pca
but what they dont say is how they
created the matrix that was fed into the pca

and the parameter values: num_components == 2 (Number of PCA components --> Reducing to 2D) num_features == 1024 (Number of hashing features the text is converted into)

We get the following output:

x	__group_x_encoded_0	__group_x_encoded_1
cant say for sure what they used but	-3.6428402533142896	0.4975849683664332
there is an r library that includes the	0.7746395720660133	0.5766808957643564
text of all jane austens works if	0.8119351913774921	0.317909114966179
you ever wanted to try some yourself	0.7487119280397619	-0.8721857902857719
they probably got the idea	-0.14175253549746752	0.5342944067879885
from	0.03192967086734439	0.06422168591488635
which uses jane austens work extensively	0.29369225493121526	0.8832400029096568
as an ongoing example when	0.7196285830258167	-0.7732919475505083
showcasing the tidytext library	0.29799339616128284	0.26371593073556526
the specific library of jane	0.7639567537897723	-0.1252943669742009
austens work is library	0.024263102404871856	0.8407417872671388
im guessing they used tidytext	-0.5380315745685673	0.8071242590633404
and janeaustenr in r, then used a slightly	-0.7396546964226968	-1.5252802271552306
different analysis	-0.0791096097809773	0.016454344219511016
using the results from r	0.39190114778641166	0.2030087287822549
in tableau or another data-viz	0.08255810541303524	0.28434896277132304
heavy program to make it palatable for	-1.0379890382908399	-0.9359229625044558
readers	0.022451788887236538	0.15649840774931126
pun intended sorry who has already	0.12645774034218657	0.5764433381841557
done a lot of	0.0645538301832265	-1.8895773134920169
really cool analysis like this	-0.8499956685146961	-0.6456009071301254
they claimed in the text that	0.31310522183662226	0.566252028711451
they used pca	-0.26125074206706816	0.5402513332430933
but what they dont say is how they	-3.1418969765868843	1.9805533718325532
created the matrix that was fed into the pca	1.3754363219912489	0.2072323270271102

Text/Reshaping

Split by delimiter and unpack into rows

Unique identifier (API): split_and_unpack

Splits the text content in the input column by the specified delimiter and unpacks the result into multiple rows, creating an additional column for position index. The delimiter can be one or more specific characters, or a regex pattern to match.

Parameters	Parameter Description	Default	Parameter Type	Suggested Values
delimiter	Delimiter to be used for splitting the string		string	[',', ';', '-', '
use_regex	Boolean flag indicating whether the specified delimiter should be treated as a regex pattern or an exact match.	False	boolean

Examples:

Given this input dataset:

('col_1', 'Text')	('col_2', 'Numeric')
star	trek
game	of
sein,field	2.23606
the	simpsons
the	big

with the column to split as "col_1" and the additional split position column name provided as "split_position" and use_regex=True, the output dataset will be:

('col_1', 'Text')	('col_2', 'Numeric')	('split_position', 'Numeric')
star	2.71828	0
trek	2.71828	1
game	3.14159	0
of	3.14159	1
thrones	3.14159	2
sein	2.23606	0
field	2.23606	1
the	1.732	0
simpsons	1.732	1
the	nan	0
big	nan	1
bang	nan	2
theory	nan	3

Text/Web

Extract URL parts from URL Text string

Unique identifier (API): extract_url_parts

Extracts URL components from a given column containing URL strings

Time Series

Resample data into a regular time series

Unique identifier (API): resample_time_series_v2

Resample data with a time-index column into a regular time series, with a specified aggregation function for each input column. Null-value interpolation is applied.

Parameters	Parameter Description	Default	Parameter Type
sampling_interval	Interval/Duration between successive records		integer
time_unit	Unit for the time interval	day	single-select

Examples:

For example, given the following dataset

receipt_time	qty	sku	expected_receipt_time
null	30	A231	2012-06-12 02:00:00
2012-06-13 00:10:00	40	A351	2012-06-13 00:00:00
2012-06-13 00:30:00	100	A351	2012-06-13 00:30:00
2012-06-13 20:45:20	29	A456	2012-06-13 00:30:00
2012-06-15 21:40:03	50	A451	2012-06-16 01:30:00
2012-06-17 05:41:23	80	A221	2012-06-20 08:30:00
2012-06-17 11:10:53	70	A221	2012-06-16 20:00:00

with time-index column receipt_time, for parameters sampling_interval = 1, time_unit = ""day"", input columns qty, sku, expected_receipt_time and the corresponding aggregation functions ""sum"", ""topk"" (k_most_frequent = 3), ""latest"", the result is as follows

receipt_time	qty	sku	expected_receipt_time
2012-06-14 00:00:00	169	["A351","A456"]	2012-06-13 00:30:00
2012-06-15 00:00:00	null	null	null
2012-06-16 00:00:00	50	["A451"]	2012-06-16 01:30:00
2012-06-17 00:00:00	null	null	null
2012-06-18 00:00:00	150	["A221"]	2012-06-20 08:30:00

Window Function

Compute window functions

Unique identifier (API): compute_window_function

Compute functions such as cumulative sum, rolling mean, etc. by specifying a window and desired functions with their inputs. Unbounded, shrinking, expanding and rolling windows are supported, with optional partitioning and ordering

Parameters	Parameter Description	Default	Parameter Type
window_start	Whether to set window start unbounded or a specified position relative to current row, with unbounded by default	unbounded	single-select
start_position	Position from which window starts, relative to the current row (0). Negative and positive numbers indicate the number of rows that precede and follow the current row respectively.		integer
window_end	Whether to set window end unbounded or a specified position relative to current row, with unbounded by default	unbounded	single-select
end_position	Position from which window ends, relative to the current row (0). Negative and positive numbers indicate the number of rows that precede and follow the current row respectively.		integer

Examples:

Given the dataset

animal	colour	birth	qty	desexed	note
cat	yellow	2019-01-01 00:00:00	10	True	There is a story
cat	blue	2019-01-03 00:00:00	9	True	No story
dog	blue	2019-01-02 00:00:00	4	True
cat	black	2019-01-07 00:00:00	2	True	No story
cat	blue	2019-01-04 00:00:00	8	True	There is a story
dog	yellow	2019-01-05 00:00:00	1	True	There is another story
dog	black	2019-01-06 00:00:00	2	False	There is a story
dog	yellow	2019-01-08 00:00:00	2	False

for input columns qty, colour, birth, desexed, note and the corresponding aggregation functions sum, most_frequent, latest, all, count (non-null), the result is as follows

animal	colour	birth	qty	desexed	note	qty_sum	colour_most_frequent	birth_latest	desexed_all	note_count
dog	blue	2019-01-02 00:00:00	4	True		4	blue	2019-01-02 00:00:00	True	0
dog	yellow	2019-01-05 00:00:00	1	True	There is another story	5	blue	2019-01-05 00:00:00	True	1
dog	black	2019-01-06 00:00:00	2	False	There is a story	7	black	2019-01-06 00:00:00	False	2
dog	yellow	2019-01-08 00:00:00	2	False		9	yellow	2019-01-08 00:00:00	False	2
cat	yellow	2019-01-01 00:00:00	10	True	There is a story	10	yellow	2019-01-01 00:00:00	True	1
cat	blue	2019-01-03 00:00:00	9	True	No story	19	blue	2019-01-03 00:00:00	True	2
cat	blue	2019-01-04 00:00:00	8	True	There is a story	27	blue	2019-01-04 00:00:00	True	3
cat	black	2019-01-07 00:00:00	2	True	No story	29	blue	2019-01-07 00:00:00	True	4

with window of unbounded start and end position 0, partitioned by animal and ordered by birth.

Rank rows

Unique identifier (API): window_rank

Rank rows after ordering and optional partitioning

Parameters	Parameter Description	Default	Parameter Type
rank_method	Method used to get the rank	rank	single-select

Examples:

For example, the following dataset

+------+------+------+
|animal|colour|credit|
+------+------+------+
|cat   |blue  |-3.0  |
|cat   |blue  |-1.0  |
|cat   |blue  |5.0   |
|cat   |blue  |7.0   |
|cat   |blue  |10.0  |
|cat   |yellow|1.0   |
|cat   |yellow|4.0   |
|cat   |yellow|4.0   |
|cat   |yellow|4.0   |
|cat   |yellow|8.0   |
|dog   |blue  |9.0   |
|dog   |blue  |7.0   |
|dog   |blue  |6.0   |
|dog   |blue  |5.0   |
|dog   |blue  |-10.0 |
|dog   |yellow|null  |
|dog   |yellow|5.0   |
|dog   |yellow|8.0   |
|dog   |yellow|null  |
|dog   |yellow|1.0   |
+------+------+------+

is converted to

+------+------+------+----+
|animal|colour|credit|rank|
+------+------+------+----+
|dog   |blue  |-10.0 |1   |
|dog   |blue  |5.0   |2   |
|dog   |blue  |6.0   |3   |
|dog   |blue  |7.0   |4   |
|dog   |blue  |9.0   |5   |
|cat   |yellow|1.0   |1   |
|cat   |yellow|4.0   |2   |
|cat   |yellow|4.0   |2   |
|cat   |yellow|4.0   |2   |
|cat   |yellow|8.0   |5   |
|cat   |blue  |-3.0  |1   |
|cat   |blue  |-1.0  |2   |
|cat   |blue  |5.0   |3   |
|cat   |blue  |7.0   |4   |
|cat   |blue  |10.0  |5   |
|dog   |yellow|null  |1   |
|dog   |yellow|null  |1   |
|dog   |yellow|1.0   |3   |
|dog   |yellow|5.0   |4   |
|dog   |yellow|8.0   |5   |
+------+------+------+----+

after applying window_rank, partitioned by animal and colour and ordered by credit for option rank.

Shift rows after ordering optionally with partitioning

Unique identifier (API): window_shift

Shift rows for input columns after ordering, optionally with partitioning, by a specified value indicating the numberof shifted rows and shifted direction

Parameters	Parameter Description	Default	Parameter Type
n_rows	Number of rows to shift where positive values indicate moving downwards (lag) and negative values refer to moving moving upwards (lead)	1	integer

Examples:

For example, given the following dataset

animal	colour	adopted	qty	vehicle
cat	yellow	2020-01-01 00:00:00	3.0	car
cat	yellow	2020-01-02 00:00:00	1.0	truck
cat	yellow	2020-01-03 00:00:00	5.0	car
cat	yellow	2020-01-04 00:00:00	7.0	van
cat	yellow	2020-01-05 00:00:00	10.0	van
cat	white	2020-01-06 00:00:00	1.0	car
cat	white	2020-01-07 00:00:00	4.0	van
cat	white	2020-01-08 00:00:00	4.0	car
cat	white	2020-01-09 00:00:00	4.0	truck
cat	white	2020-01-10 00:00:00	8.0	car
dog	yellow	2020-01-11 00:00:00	9.0	car
dog	yellow	2020-01-12 00:00:00	7.0	van
dog	yellow	2020-01-13 00:00:00	6.0	car
dog	yellow	2020-01-14 00:00:00	5.0	van
dog	yellow	2020-01-15 00:00:00	10.0	car
dog	white	2020-01-16 00:00:00	null	van
dog	white	2020-01-17 00:00:00	5.0	car
dog	white	2020-01-18 00:00:00	8.0	car
dog	white	2020-01-19 00:00:00	null	truck
dog	white	2020-01-20 00:00:00	1.0	car

the result is

animal	colour	adopted	qty	vehicle	qty_adjusted	vehicle_adjusted
cat	white	2020-01-06 00:00:00	1.0	car	4.0	van
cat	white	2020-01-07 00:00:00	4.0	van	4.0	car
cat	white	2020-01-08 00:00:00	4.0	car	4.0	truck
cat	white	2020-01-09 00:00:00	4.0	truck	8.0	car
cat	white	2020-01-10 00:00:00	8.0	car	null	null
cat	yellow	2020-01-01 00:00:00	3.0	car	1.0	truck
cat	yellow	2020-01-02 00:00:00	1.0	truck	5.0	car
cat	yellow	2020-01-03 00:00:00	5.0	car	7.0	van
cat	yellow	2020-01-04 00:00:00	7.0	van	10.0	van
cat	yellow	2020-01-05 00:00:00	10.0	van	null	null
dog	white	2020-01-16 00:00:00	null	van	5.0	car
dog	white	2020-01-17 00:00:00	5.0	car	8.0	car
dog	white	2020-01-18 00:00:00	8.0	car	null	truck
dog	white	2020-01-19 00:00:00	null	truck	1.0	car
dog	white	2020-01-20 00:00:00	1.0	car	null	null
dog	yellow	2020-01-11 00:00:00	9.0	car	7.0	van
dog	yellow	2020-01-12 00:00:00	7.0	van	6.0	car
dog	yellow	2020-01-13 00:00:00	6.0	car	5.0	van
dog	yellow	2020-01-14 00:00:00	5.0	van	10.0	car
dog	yellow	2020-01-15 00:00:00	10.0	car	null	null

with input columns qty and vehicle, partitioned by animal and colour and ordered by adopted, for shifted n_rows = -1 (lead).

Deprecated actions

The following actions that were available previously have now been deprecated. This means that you cannot find them any more on the recipe UI, as they are substituted by newer actions that are better in terms of usability or performance. However, existing recipes that contain these actions will continue to be supported.

Column Group

Take

Unique identifier (API): take

Please use the newer version get_column_group_components

Extracts specified components from a column group. If the component is not found, returns a null column.

Parameters	Parameter Description	Default	Parameter Type
address_type	Method of selection of group column component to take		single-select
index	The component index (zero based) to take from the group. E.g.If the group columns are ["__group_grp_a", "__group_grp_b"] and index==1, the taken column will be "__group_grp_b".	0	integer
suffix	The component suffix to take from the group. E.g.If the group columns are ["__group_grp_a", "__group_grp_b"] and suffix=="a", the taken column will be "__group_grp_a".		string

Examples:

For the following set of parameters:

parameters = {'address_type': 'index', 'index': 1}

and the following input table:

__group_grp_a_0	__group_grp_a_1	__group_grp_b_0	__group_grp_b_1
1	2	1	null
Sentence A	Sentence B	Sentence C	Sentence D

The output is:

__group_grp_a_0	__group_grp_a_1	__group_grp_b_0	__group_grp_b_1	col_from_grp_a	col_from_grp_b
1	2	1	null	2	null
Sentence A	Sentence B	Sentence C	Sentence D	Sentence B	Sentence D

Custom Formula

Create a new column with a formula

Unique identifier (API): create_new_column_with_formula

Please use the new version create_new_column_with_formula_v2

Creates a new column with any user-entered formula. Common Excel/SQL functions and operators for manipulating Numeric, DateTime, Text, and Boolean values are permitted. For a full list of allowed functions, see documentation.

Parameters	Parameter Description	Default	Parameter Type
expression	Any formula where symbols correspond to the input columns in lexical order. For eg., if the input selection has column_B then column_A and if the formula is y / x, column_B is mapped to x and column_A is mapped to y.		string

Examples:

Consider the dataset:

manufacturer	model	year	displ	cty	hwy
audi	a4 quattro	1999	1.8	16	25
audi	a6 quattro	1999	2.8	15	24
audi	a6 quattro	2008	4.2	16	23
audi	a6 quattro	2008	4.2	16	23
chevrolet	c1500 suburban 2wd	2008	6	12	17
chevrolet	corvette	1999	5.7	16	26
chevrolet	corvette	1999	5.7	15	23
chevrolet	k1500 tahoe 4wd	1999	6.5	14	17
chevrolet	malibu	1999	3.1	18	26
dodge	caravan 2wd	1999	3.8	15	22

where cty, hwy and year are of Numeric type.

Given the input columns selected as ['cty', 'hwy'], the output column name 'hwy_minus_cty', the formula 'y - x', the result will be:

manufacturer	model	year	displ	cty	hwy	hwy_minus_cty
audi	a4 quattro	1999	1.8	16	25	9
audi	a6 quattro	1999	2.8	15	24	9
audi	a6 quattro	2008	4.2	16	23	7
audi	a6 quattro	2008	4.2	16	23	7
chevrolet	c1500 suburban 2wd	2008	6	12	17	5
chevrolet	corvette	1999	5.7	16	26	10
chevrolet	corvette	1999	5.7	15	23	8
chevrolet	k1500 tahoe 4wd	1999	6.5	14	17	3
chevrolet	malibu	1999	3.1	18	26	8
dodge	caravan 2wd	1999	3.8	15	22	7

where the new hwy_minus_cty column will be of Numeric type.

For the same input data, given the input columns selected as ['year'], the output column name 'century' the formula 'IF(x <= 2000, "20th", "21st")' the result will be:

manufacturer	model	year	displ	cty	hwy	century
audi	a4 quattro	1999	1.8	16	25	20th
audi	a6 quattro	1999	2.8	15	24	20th
audi	a6 quattro	2008	4.2	16	23	21st
audi	a6 quattro	2008	4.2	16	23	21st
chevrolet	c1500 suburban 2wd	2008	6	12	17	21st
chevrolet	corvette	1999	5.7	16	26	20th
chevrolet	corvette	1999	5.7	15	23	20th
chevrolet	k1500 tahoe 4wd	1999	6.5	14	17	20th
chevrolet	malibu	1999	3.1	18	26	20th
dodge	caravan 2wd	1999	3.8	15	22	20th

where the new century column will be of Text type.

Transform columns with a formula

Unique identifier (API): transform_columns_with_formula

Please use the new version transform_columns_with_formula_v2

Transform one or more column(s) with a user-specified formula. Common Excel/SQL functions and operators for manipulating Numeric, DateTime, Text and Boolean values are permitted. For a full list of these functions, see documentation.

Parameters	Parameter Description	Default	Parameter Type
formula	Formula where symbol SELF refers to the column(s) to be transformed, and the rest symbols in lexical order are mapped to the other input column(s) if any.For example, if the columns to be transformed are col_1 and col_2, other input columns are col_A and col_B, with formula SELF + z - y, col_i is modified as col_i + col_B - col_A for i = 1, 2		string

Examples:

For example, given the following dataset

qty_1	qty_2
10	nan
20	15
15	6
nan	1
8	8

and formula = ""SELF + (y + SELF) * 10"", with qty_1 as column to be transformed and qty_2 as the other input column, the result will be

qty_1	qty_2
nan	nan
370	15
225	6
nan	1
168	8

Data Cleaning/Processing

Remove duplicated rows within group

Unique identifier (API): group_by_remove_duplicates

Please use the newer version group_by

Remove duplicated rows for one or more input columns grouped by other selected column(s).

Parameters	Parameter Description	Default	Parameter Type
keep	First or last row to keep	first	single-select

Examples:

For example, given the following dataset

animal	colour	carried	description	qty	time	desexed
cat	yellow	van	There is a story.	0.5416980271485203	2020-10-10 00:00:00	true
cat	yellow	van	There is a story.	0.09442987061798414	2020-10-10 00:00:00	false
cat	yellow	car	There is a story.	0.5766948082733302	2020-10-10 00:00:00	true
cat	yellow	van	There is a story.	0.8015245589198161	2020-01-01 00:00:00	false
cat	yellow	van	There is a story.	0.2523252855972161	2019-01-01 00:00:00	true
cat	white	van	There is a story.	0.044270295367698886	2020-01-01 00:00:00	true
cat	white	car	There is another story.	0.34340627533339274	2020-01-01 00:00:00	true
cat	white	van	There is another story.	0.243885710885979	2019-01-10 00:00:00	true
cat	white	car	There is another story.	0.9875301137422845	2020-01-01 00:00:00	false
cat	white	van	There is a story.	0.4416194595201147	2019-01-10 00:00:00	false
dog	yellow	van	There is another story.	0.18251646225487406	2019-01-10 00:00:00	false
dog	yellow	car	There is a story.	0.9073423733435338	2019-01-10 00:00:00	false
dog	yellow	car	There is another story.	0.5242232544087159	2020-01-01 00:00:00	false
dog	yellow	van	There is a story.	0.09555633797329921	2019-01-10 00:00:00	true
dog	yellow	car	There is a story.	0.13287387217823854	2020-10-10 00:00:00	true
dog	white	car	There is another story.	0.8686814568933312	2020-10-02 00:00:00	true
dog	white	car	There is another story.	0.5376552548800148	2019-01-10 00:00:00	true
dog	white	car	There is another story.	0.6979028231254814	2019-01-10 00:00:00	false
dog	white	van	There is a story.	0.19022329903980462	2020-01-01 00:00:00	true
dog	white	car	There is another story.	0.856685303343519	2020-01-01 00:00:00	false

with input columns animal and colour, keep=""first"", the result is

animal	colour	carried	description	qty	time	desexed
cat	yellow	van	There is a story.	0.5416980271485203	2020-10-10 00:00:00	true
cat	white	van	There is a story.	0.044270295367698886	2020-01-01 00:00:00	true
dog	yellow	van	There is another story.	0.18251646225487406	2019-01-10 00:00:00	false
dog	white	car	There is another story.	0.8686814568933312	2020-10-02 00:00:00	true

Impute columns

Unique identifier (API): impute_column

Please use the newer version impute_numeric or impute_categorical

Impute the missing values in your data

Parameters	Parameter Description	Default	Parameter Type
categorical_strategy	Strategy for imputing Categorical columns	most_frequent	single-select
categorical_fill_value	Filler value for Categoric columns when strategy is constant		string
numeric_strategy	Strategy for imputing Numeric columns	mean	single-select
numeric_fill_value	Filler value for Numeric columns when strategy is constant		float

Feature Engineering

Calculate math function

Unique identifier (API): calculate_math_function

Please use the action create_new_column_with_formula_v2

Calculate a math function on selected columns. Invalid values such as negative input for a logarithmic function result in null outputs.

Parameters	Parameter Description	Default	Parameter Type
math_function	Math function applied to columns		single-select

Examples:

For example, compute the absolute value abs(x).

Calculate math function (2)

Unique identifier (API): calculate_math_function2

Please use the action create_new_column_with_formula_v2

Calculate a math function that takes two inputs. If invalid inputs are encountered (such as division by zero), the outputs will be null.

Parameters	Parameter Description	Default	Parameter Type
math_function	Math function applied to input columns		single-select

Examples:

For example compute the difference (c = a -b) or computing the ratio (c = a/b)

Cluster and extract features

Unique identifier (API): cluster

Please use the newer version get_cluster_features_v2

Cluster the input data using unsupervised learning algorithms. Outputs three features - the cluster ID, cluster size and euclidian distance from the centroid of the cluster.

Parameters	Parameter Description	Default	Parameter Type	Parameter Min	Parameter Max	Parameter Step
algorithm	The algorithm used for clustering, such as k-means or Gaussian Mixture Model.		single-select
num_clusters_method	The method that is utilized for calculating the number of clusters	inferred	single-select
num_clusters	The numbers of clusters to generate.	2	range	2.0	100.0	1.0
max_num_clusters	This defines the search space for the algorithm inferring the best number of clusters automatically. Setting it to a value of K will make the algorithm look for the best number of clusters in the range [2, 3, ..., K]	10	range	2.0	100.0	1.0
seed	Nonnegative integer. Random seed for clustering algorithms. Used for obtaining repeatable results.	0	integer
tolerance	The minimum change in the squared distances between cluster centroids between two successive iterations, for the algorithm to continue to the next iteration. If the change is below the threshold specified by this parameter, the algorithm is assumed to have converged before reaching the maximum number of iterations. A lower value for this parameter results in higher fidelity in the assignment of cluster IDs, but can make the action run slower especially on large datasets.	0.0001	float
max_iter	The maximum number of iterations regardless of convergence. The higher it is the more accurate the clustering will be, but comes at the cost of a longer runtime.	20	integer
min_cluster_size	The smallest size grouping that HDBSCAN considers as a cluster. Single linkage splits that contain fewer points than this will be considered points "falling out" of a cluster rather than a cluster splitting into two new clusters	5	integer
min_samples_specified	If enabled, allows specifying the minimum number of samples parameter. Otherwise, will be determined automatically.	False	boolean
min_samples	The number of samples in a neighbourhood for a point to be considered a core point. Providing a larger value for this parameter results in a more conservative clustering, which means more points will be declared as noise and clusters will be restricted to progressively more dense areas.	1	integer
cluster_selection_epsilon	A distance threshold. Clusters below this value will be merged. The choice of cluster_selection_epsilon depends on the given distances between your data points. For example, set the value to 0.5 if you don't want to separate clusters that are less than 0.5 units apart.	0	float
allow_single_cluster	By default HDBSCAN* will not produce a single cluster, setting this to True will override this and allow single cluster results in the case that you feel this is a valid result for your dataset.	False	boolean

Extract PCA Components

Unique identifier (API): extract_pca

Please use the newer version extract_pca_v2

Create new numerical features by combining existing numerical features in linear ways using PCA

Parameters	Parameter Description	Default	Parameter Type	Parameter Min	Parameter Max	Parameter Step
limit_components_by	Criterion for selecting the principal components.If the minimum explained variance is used, the components that contribute most to the variance are selected until the cumulative variance is not less than the specified ratio of the total variance.	min_explained_variance	single-select
num_components	The number of principal components to extract from the input data		integer
min_explained_variance	This is the threshold on the ratio of explained variance of a component to the total variance. Only the components with highest variance whose cumulative variance is at least the specified ratio of total variance will be selected	0.8	range	0.01	1.0	0.01
categorical_impute_method	Method for imputing Categorical columns	most_frequent	single-select
categorical_fill_value	Filler value for categorical columns when strategy is constant		string
numeric_impute_method	Method for imputing Numeric columns	mean	single-select
numeric_fill_value	Filler value for Numeric columns when strategy is constant		float

One hot encode categorical columns

Unique identifier (API): one_hot_encode

Please use the newer version one_hot_encode_v2

This action converts categorical columns by one-hot encoding. Missing values remain as null. Unseen values are encoded as all 0s, when a finalized recipe with this action is applied on new data

Examples:

For example, given the following dataset

col_1	col_2
A	yellow
B	null
B	blue
A	blue
E	red

the result is

col_1	col_2	__group_encoded_1_0	__group_encoded_1_1	__group_encoded_1_2	__group_encoded_2_0	__group_encoded_2_1	__group_encoded_2_2
A	yellow	1	0	0	0	0	1
B	null	0	1	0	null	null	null
B	blue	0	1	0	1	0	0
A	blue	1	0	0	1	0	0
E	red	0	0	1	0	1	0

Filtering/Custom Formula

Filter rows

Unique identifier (API): filter_rows_with_formula

Please use the new version filter_rows_with_formula_v2

Filter rows in data by keeping only rows that satisfy the condition specified in user-entered formula. Common Excel/SQL functions and operators for manipulating Numeric, DateTime, Text, and Boolean values are permitted. For a full list of allowed functions, see documentation.

NOTE: The formula entered must evaluate to a boolean value.

Parameters	Parameter Description	Default	Parameter Type
condition	The condition under which the rows are to be kept, entered as a formula. Symbols must correspond to the input columns in lexical order. For eg., if the input selection has column_B then column_A and if the formula is y >= x, column_B is mapped to x and column_A is mapped to y.		string

Examples:

Consider the dataset:

manufacturer	model	year	displ	cty	hwy
audi	a4 quattro	1999	1.8	16	25
audi	a6 quattro	1999	2.8	15	24
audi	a6 quattro	2008	4.2	16	23
audi	a6 quattro	2008	4.2	16	23
chevrolet	c1500 suburban 2wd	2008	6	12	17
chevrolet	corvette	1999	5.7	16	26
chevrolet	corvette	1999	5.7	15	23
chevrolet	k1500 tahoe 4wd	1999	6.5	14	17
chevrolet	malibu	1999	3.1	18	26
dodge	caravan 2wd	1999	3.8	15	22
dodge	caravan 2wd	2008	4	16	23
dodge	durango 4wd	2008	5.7	13	18
dodge	durango 4wd	2008	5.7	13	18
dodge	ram 1500 pickup 4wd	1999	5.9	11	15
ford	expedition 2wd	1999	4.6	11	17
ford	explorer 4wd	1999	5	13	17
ford	f150 pickup 4wd	1999	4.2	14	17
ford	f150 pickup 4wd	1999	4.2	14	17
ford	f150 pickup 4wd	2008	4.6	13	17
ford	mustang	2008	4	17	26

where cty and year are of Numeric type.

Given the input columns selected as ['year', 'cty'] and the formula 'AND(x > 2000, y >= 15)', the result will be:

manufacturer	model	year	displ	cty	hwy
audi	a6 quattro	2008	4.2	16	23
audi	a6 quattro	2008	4.2	16	23
dodge	caravan 2wd	2008	4	16	23
ford	mustang	2008	4	17	26

Reshaping

Pivot a column with a specified aggregation

Unique identifier (API): pivot

Please use the newer version pivot_v2

Pivot a column and perform a specified aggregation on one or more numeric columns, grouped by other selected columns

Parameters	Parameter Description	Default	Parameter Type	Parameter Min	Parameter Max	Parameter Step
aggregation_function	Aggregation function applied to input column(s)	mean	single-select
median_relative_error	Relative error for approximating median. Must be no less than 0.001 for computational efficiency.	0.001	range	0.001	1.0	0.001
count_distinct_maximum_error	Maximum estimation error allowed for approximating the number of distinct values. Must be no less than 0.01 for computational efficiency.	0.01	range	0.01	1.0	0.01
pivot_values	List of values that are converted to columns in the output	[]	string-list

Examples:

For example, given the following dataset

animal	size	qty
cat	small	95
cat	medium	67
cat	large	39
cat	huge	45
cat	small	50
cat	medium	18
cat	large	37
cat	huge	22
cat	small	12
cat	medium	45
dog	large	84
dog	huge	48
dog	small	71
dog	medium	95
dog	large	87
dog	huge	35
dog	small	7
dog	medium	25
dog	large	42
dog	huge	45

with input pivot column size, values column qty and aggregation function sum, grouped by column animal, the result is

animal	__group_qty_agg_huge	__group_qty_agg_large	__group_qty_agg_medium	__group_qty_agg_small
dog	128	213	120	78
cat	67	76	130	157

Unpack values to multiple rows

Unique identifier (API): unpack

Please use the newer version unpack_v2

Reshape a dataset by unpacking one or more column group(s) and create a position indication column

Text

Extract capture groups

Unique identifier (API): extract_capture_groups

Please use the newer versions of extract_capture_groups

Extracts one or more regex capture groups into a column group

Parameters	Parameter Description	Default	Parameter Type
pattern	A regular expression pattern containing one or more capture groups		string

Examples:

For the following pattern == '([aeiou].)([aeiou].)' , the following input table and assuming we apply the action over both columns ([col_1, col_2]):

col_1	col_2
New_York	bc
Lisbon	df
Tokyo	gh
Paris	jk
null	lm
Munich	np

The expected output is:

col_1	col_2	__group_grp_a_0	__group_grp_a_1	__group_grp_b_0	__group_grp_b_1
New_York	bc	null	null	null	null
Lisbon	df	null	null	null	null
Tokyo	gh	null	null	null	null
Paris	jk	ar	is	null	null
null	lm	null	null	null	null
Munich	np	un	ic	null	null

Find all matches of a regular expression

Unique identifier (API): regex_findall

Please use the new action extract_all_regex_matches

Creates an output column group containing all the strings that match the regex pattern.

Parameters	Parameter Description	Default	Parameter Type
pattern	A regular expression pattern for the matching		string

Examples:

For pattern == '\d\d' (match non overlapping double digits) and the following input table:

col_1
null
Test 54 45 Test
Dummy sentence
Just some number 00

The output is:

col_1	__group_col_1_re_findall_0	__group_col_1_re_findall_1
null	null	null
Test 54 45 Test	54	45
Dummy sentence	null	null
Just some number 00	00	null

Split text column on delimiter

Unique identifier (API): split_on_delimiter

Please use either split_on_delimiter_to_multicol or split_to_array.

For a given column, extracts text components between the delimiters into a column group. The size of the column group corresponds to the largest number of components found in the column or by the limit on the number of splits. Trailing null values are created for records containing fewer components.

Parameters	Parameter Description	Default	Parameter Type	Suggested Values
delimiter	Delimiter to be used for splitting the string.		string	[',', ';', '-', '
delimiter_type	Type of delimiter	Verbatim	single-select
limit_num_splits	Boolean flag indicating wether you wish to limit the number of splits or not.	False	boolean
num_of_splits	An integer which controls the number of times the delimiter regex is applied.	2	integer

Examples:

For the following set of parameters:

parameters = {'delimiter': ':', 'limit_num_splits': True, 'num_of_splits': 2}

and the following input table:

col_1
one:two:three
no_split

The expected output is:

col_1	__group_col_1_split_grp_0	__group_col_1_split_grp_1
one:two:three	one	two:three
no_split	no_split	null

Text/JSON

Split JSON array column into a column group

Unique identifier (API): split_json_array

Please use unpack_v2

Every row (or cell) of the input column is assumed to be a JSON array stored as a string. Extract each element in the array into a separate column component in a columngroup.

Parameters	Parameter Description	Default	Parameter Type
path	Path to the JSON array component via (Spark-flavor) JSON path. Defaults to $ (the top-level).	$	string

Time Series

Resample data into a regular time series

Unique identifier (API): resample_time_series

Please use the newer version resample_time_series_v2

Resample data with a time-index column into a regular time series, with aggregation for all columns except the nominated time index. Null-value interpolation is applied.

Parameters	Parameter Description	Default	Parameter Type
sampling_interval	Length of intervals between successive records		integer
time_unit	Time unit for intervals between successive records	day	single-select
aggregation_function	Aggregation function to be applied	last	single-select

Examples:

For example, given the following dataset

receipt_time	qty	sku	expected_receipt_time
null	30	A231	2012-06-12 02:00:00
2012-06-13 00:00:00	40	A351	2012-06-15 10:00:00
2012-06-13 20:45:20	29	A456	2012-06-13 00:30:00
2012-06-15 21:40:03	50	A451	2012-06-16 01:30:00
2012-06-17 05:41:23	80	A223	2012-06-16 20:30:00
null	100	A221	2012-06-20 08:30:00
2012-06-17 11:10:53	70	A264	2012-06-16 20:00:00

with time-index column receipt_time for parameters sampling_interval = 12, time_unit = ""hour"" and aggregation_function = ""last"", the result is

receipt_time	qty	sku	expected_receipt_time
2012-06-13 00:00:00	40	A351	2012-06-15 10:00:00
2012-06-13 12:00:00	null	null	null
2012-06-14 00:00:00	29	A456	2012-06-13 00:30:00
2012-06-14 12:00:00	null	null	null
2012-06-15 00:00:00	null	null	null
2012-06-15 12:00:00	null	null	null
2012-06-16 00:00:00	50	A451	2012-06-16 01:30:00
2012-06-16 12:00:00	null	null	null
2012-06-17 00:00:00	null	null	null
2012-06-17 12:00:00	70	A264	2012-06-16 20:00:00

Window Function

Count the number of values over partition

Unique identifier (API): window_aggregate_count

Please use the newer version compute_rolling_window_function

This action counts the number of values in one or more input column(s) partitioned by other selected column(s).

Parameters	Parameter Description	Default	Parameter Type
value_type	What to count. The default is to count only non-null values.For approximately counting distinct values (see https://en.wikipedia.org/wiki/HyperLogLog), null values are ignored and the allowed estimation error is 0.05 at maximum	non_null	single-select

Examples:

For example, given the following dataset

colour	vehicle	animal	description	qty	time	verified
blue	car	cat	There is another story.	0.9371423365841736	null	true
blue	van	null	null	0.504013991387411	2019-01-02 00:00:00	true
yellow	car	cat	There is another story.	0.7664983791343694	2019-01-03 00:00:00	false
yellow	van	null	null	0.391325429830197	2019-01-04 00:00:00	true
yellow	car	cat	There is another story.	null	2019-01-05 00:00:00	true
blue	car	null	There is another story.	null	2019-01-06 00:00:00	true
yellow	car	dog	There is another story.	null	null	true
blue	van	dog	There is another story.	0.8223637606171873	null	false
blue	van	cat	There is another story.	0.9805952019930625	2019-01-09 00:00:00	false
yellow	van	dog	null	null	2019-01-10 00:00:00	true
blue	van	null	null	null	2019-01-11 00:00:00	true
blue	car	null	There is another story.	0.010846767436331839	2019-01-12 00:00:00	false
blue	car	cat	There is a story.	0.30042645923211464	2019-01-13 00:00:00	true
yellow	van	dog	There is a story.	0.7759473050527014	2019-01-14 00:00:00	true
yellow	van	dog	There is another story.	0.5250835307237919	2019-01-15 00:00:00	true
yellow	car	dog	There is another story.	0.4708643894438649	null	true
blue	van	dog	null	0.8425979127421865	null	false
yellow	van	dog	There is another story.	0.9901884866319394	2019-01-18 00:00:00	true
blue	car	dog	There is a story.	0.6662684130667034	2019-01-19 00:00:00	true
blue	car	cat	There is another story.	null	2019-01-20 00:00:00	true

with input columns animal, description, qty, time, verified partitioned by colour and vehicle for option non_null, the result is

colour	vehicle	animal	description	qty	time	verified	animal_count	description_count	qty_count	time_count	verified_count
blue	car	cat	There is another story.	0.9371423365841736	null	true	4	6	4	5	6
blue	van	null	null	0.504013991387411	2019-01-02 00:00:00	true	3	2	4	3	5
yellow	car	cat	There is another story.	0.7664983791343694	2019-01-03 00:00:00	false	4	4	2	2	4
yellow	van	null	null	0.391325429830197	2019-01-04 00:00:00	true	4	3	4	5	5
yellow	car	cat	There is another story.	null	2019-01-05 00:00:00	true	4	4	2	2	4
blue	car	null	There is another story.	null	2019-01-06 00:00:00	true	4	6	4	5	6
yellow	car	dog	There is another story.	null	null	true	4	4	2	2	4
blue	van	dog	There is another story.	0.8223637606171873	null	false	3	2	4	3	5
blue	van	cat	There is another story.	0.9805952019930625	2019-01-09 00:00:00	false	3	2	4	3	5
yellow	van	dog	null	null	2019-01-10 00:00:00	true	4	3	4	5	5
blue	van	null	null	null	2019-01-11 00:00:00	true	3	2	4	3	5
blue	car	null	There is another story.	0.010846767436331839	2019-01-12 00:00:00	false	4	6	4	5	6
blue	car	cat	There is a story.	0.30042645923211464	2019-01-13 00:00:00	true	4	6	4	5	6
yellow	van	dog	There is a story.	0.7759473050527014	2019-01-14 00:00:00	true	4	3	4	5	5
yellow	van	dog	There is another story.	0.5250835307237919	2019-01-15 00:00:00	true	4	3	4	5	5
yellow	car	dog	There is another story.	0.4708643894438649	null	true	4	4	2	2	4
blue	van	dog	null	0.8425979127421865	null	false	3	2	4	3	5
yellow	van	dog	There is another story.	0.9901884866319394	2019-01-18 00:00:00	true	4	3	4	5	5
blue	car	dog	There is a story.	0.6662684130667034	2019-01-19 00:00:00	true	4	6	4	5	6
blue	car	cat	There is another story.	null	2019-01-20 00:00:00	true	4	6	4	5	6

Get DateTime value by partition

Unique identifier (API): window_aggregate_datetime

Please use the newer version compute_rolling_window_function

This action applies a window aggregation function on one or more DateTime column(s) partitioned by other selected column(s).

Parameters	Parameter Description	Default	Parameter Type
agg_function	Aggregation function applied to input column(s)		single-select

Examples:

For example, given the following dataset

animal	colour	time_born	time_adopted
cat	blue	2019-01-01 00:00:00	null
cat	yellow	2019-01-02 00:00:00	2019-01-01 00:00:00
dog	yellow	2019-01-03 00:00:00	2019-01-01 00:00:00
cat	yellow	2019-01-04 00:00:00	null
cat	yellow	2019-01-05 00:00:00	null
dog	blue	2019-01-06 00:00:00	2019-01-02 00:00:00
cat	blue	2019-01-07 00:00:00	2019-01-02 00:00:00
cat	blue	null	2019-01-02 00:00:00
dog	yellow	2019-01-09 00:00:00	2019-01-02 00:00:00
cat	yellow	2019-01-10 00:00:00	2019-01-02 00:00:00
dog	blue	null	null
cat	blue	2019-01-12 00:00:00	2019-01-03 00:00:00
dog	blue	null	2019-01-03 00:00:00
cat	blue	2019-01-14 00:00:00	2019-01-03 00:00:00
cat	blue	null	2019-01-03 00:00:00
cat	blue	2019-01-16 00:00:00	2019-01-04 00:00:00
cat	blue	2019-01-17 00:00:00	2019-01-04 00:00:00
cat	yellow	null	2019-01-04 00:00:00
dog	blue	2019-01-19 00:00:00	null
dog	yellow	2019-01-20 00:00:00	2019-01-04 00:00:00

with input columns time_born and time_adopted, partitioned by animal and colour for option earliest, the result is

animal	colour	time_born	time_adopted	agg_birth	agg_adoption
cat	blue	2019-01-01 00:00:00	null	2019-01-01 00:00:00	2019-01-02 00:00:00
cat	yellow	2019-01-02 00:00:00	2019-01-01 00:00:00	2019-01-02 00:00:00	2019-01-01 00:00:00
dog	yellow	2019-01-03 00:00:00	2019-01-01 00:00:00	2019-01-03 00:00:00	2019-01-01 00:00:00
cat	yellow	2019-01-04 00:00:00	null	2019-01-02 00:00:00	2019-01-01 00:00:00
cat	yellow	2019-01-05 00:00:00	null	2019-01-02 00:00:00	2019-01-01 00:00:00
dog	blue	2019-01-06 00:00:00	2019-01-02 00:00:00	2019-01-06 00:00:00	2019-01-02 00:00:00
cat	blue	2019-01-07 00:00:00	2019-01-02 00:00:00	2019-01-01 00:00:00	2019-01-02 00:00:00
cat	blue	null	2019-01-02 00:00:00	2019-01-01 00:00:00	2019-01-02 00:00:00
dog	yellow	2019-01-09 00:00:00	2019-01-02 00:00:00	2019-01-03 00:00:00	2019-01-01 00:00:00
cat	yellow	2019-01-10 00:00:00	2019-01-02 00:00:00	2019-01-02 00:00:00	2019-01-01 00:00:00
dog	blue	null	null	2019-01-06 00:00:00	2019-01-02 00:00:00
cat	blue	2019-01-12 00:00:00	2019-01-03 00:00:00	2019-01-01 00:00:00	2019-01-02 00:00:00
dog	blue	null	2019-01-03 00:00:00	2019-01-06 00:00:00	2019-01-02 00:00:00
cat	blue	2019-01-14 00:00:00	2019-01-03 00:00:00	2019-01-01 00:00:00	2019-01-02 00:00:00
cat	blue	null	2019-01-03 00:00:00	2019-01-01 00:00:00	2019-01-02 00:00:00
cat	blue	2019-01-16 00:00:00	2019-01-04 00:00:00	2019-01-01 00:00:00	2019-01-02 00:00:00
cat	blue	2019-01-17 00:00:00	2019-01-04 00:00:00	2019-01-01 00:00:00	2019-01-02 00:00:00
cat	yellow	null	2019-01-04 00:00:00	2019-01-02 00:00:00	2019-01-01 00:00:00
dog	blue	2019-01-19 00:00:00	null	2019-01-06 00:00:00	2019-01-02 00:00:00
dog	yellow	2019-01-20 00:00:00	2019-01-04 00:00:00	2019-01-03 00:00:00	2019-01-01 00:00:00

Make a boolean indicator over partition

Unique identifier (API): window_aggregate_indicate_boolean

Please use the newer version compute_rolling_window_function

This action applies a window aggregation function as an overall indicator on one or more boolean columns partitioned by other selected column(s).

Parameters	Parameter Description	Default	Parameter Type
indicator_function	Indicator function applied to input column(s)		single-select

Examples:

For example, given the following dataset

animal	colour	adopted	registered
cat	blue	true	true
dog	yellow	true	false
cat	yellow	false	true
cat	blue	false	true
dog	blue	false	true
dog	blue	false	false
cat	yellow	true	true
dog	yellow	true	true
dog	yellow	true	true
dog	blue	true	false
dog	yellow	false	false
dog	yellow	false	false
dog	blue	false	true
cat	yellow	true	false
cat	yellow	true	true
dog	blue	false	false
dog	yellow	true	true
dog	blue	false	false
cat	yellow	false	false
dog	blue	false	false

with input columns adopted and registered, partitioned by animal and colour for option any, the result is

animal	colour	adopted	registered	agg_adopted	agg_registered
cat	blue	true	true	true	true
dog	yellow	true	false	true	true
cat	yellow	false	true	true	true
cat	blue	false	true	true	true
dog	blue	false	true	true	true
dog	blue	false	false	true	true
cat	yellow	true	true	true	true
dog	yellow	true	true	true	true
dog	yellow	true	true	true	true
dog	blue	true	false	true	true
dog	yellow	false	false	true	true
dog	yellow	false	false	true	true
dog	blue	false	true	true	true
cat	yellow	true	false	true	true
cat	yellow	true	true	true	true
dog	blue	false	false	true	true
dog	yellow	true	true	true	true
dog	blue	false	false	true	true
cat	yellow	false	false	true	true
dog	blue	false	false	true	true

Aggregate numeric column(s) over partition

Unique identifier (API): window_aggregate_numeric

Please use the newer version compute_rolling_window_function

This action applies a window aggregation function on one or more numeric columns partitioned by other selected column(s).

Parameters	Parameter Description	Default	Parameter Type	Parameter Min	Parameter Max	Parameter Step
agg_function	Aggregation function applied to input column(s)		single-select
relative_error	Relative error for approximating median or a specific quantile. Must be no less than 0.001 for computational efficiency.		range	0.001	1.0	0.001
quantile_to_compute	Specify which quantile to compute for the quantile aggregation function. Must be a number between 0 and 1.		range	0.0	1.0	0.05

Examples:

For example, given the following dataset

colour	vehicle	qty_1	qty_2
yellow	van	0.11247070608569998	5
yellow	van	0.28328160937677216	-15
blue	car	0.517063017219678	19
blue	van	0.5301237478473354	-9
blue	van	0.3620932896270347	10
yellow	car	0.4225691387726025	15
yellow	car	0.290901753183477	-19
blue	van	0.8861529756930526	19
yellow	van	0.0663419211386469	12
blue	car	0.5966970598200722	-4
blue	car	0.420710037504774	-8
yellow	van	0.6275038840320153	-14
yellow	car	0.9498072805894898	-16
blue	car	0.9065231957598833	-3
yellow	van	0.6876214007342292	-14
blue	van	0.12799068964691773	-16
yellow	car	0.8456065368763648	-20
yellow	van	0.7136156920988985	10
yellow	van	0.31253912094683933	-16
yellow	van	0.8326289665837018	-20

with input columns qty_1 and qty_2 and aggregation function mean partitioned by colour and vehicle, the result is

colour	vehicle	qty_1	qty_2	aggregation_1	aggregation_2
yellow	van	0.11247070608569998	5	0.4545004126246004	-6.5
yellow	van	0.28328160937677216	-15	0.4545004126246004	-6.5
blue	car	0.517063017219678	19	0.6102483275761019	1.0
blue	van	0.5301237478473354	-9	0.4765901757035851	1.0
blue	van	0.3620932896270347	10	0.4765901757035851	1.0
yellow	car	0.4225691387726025	15	0.6272211773554836	-10.0
yellow	car	0.290901753183477	-19	0.6272211773554836	-10.0
blue	van	0.8861529756930526	19	0.4765901757035851	1.0
yellow	van	0.0663419211386469	12	0.4545004126246004	-6.5
blue	car	0.5966970598200722	-4	0.6102483275761019	1.0
blue	car	0.420710037504774	-8	0.6102483275761019	1.0
yellow	van	0.6275038840320153	-14	0.4545004126246004	-6.5
yellow	car	0.9498072805894898	-16	0.6272211773554836	-10.0
blue	car	0.9065231957598833	-3	0.6102483275761019	1.0
yellow	van	0.6876214007342292	-14	0.4545004126246004	-6.5
blue	van	0.12799068964691773	-16	0.4765901757035851	1.0
yellow	car	0.8456065368763648	-20	0.6272211773554836	-10.0
yellow	van	0.7136156920988985	10	0.4545004126246004	-6.5
yellow	van	0.31253912094683933	-16	0.4545004126246004	-6.5
yellow	van	0.8326289665837018	-20	0.4545004126246004	-6.5

Window Function/Rolling

Make a rolling boolean indicator with optional partitioning

Unique identifier (API): window_rolling_boolean

Please use the newer version compute_rolling_window_function

Compute an aggregation as a rolling indicator on one or more boolean columns, optionally partitioned by other selected column(s).

Parameters	Parameter Description	Default	Parameter Type
indicator_function	Indicator function applied to input column(s)		single-select
window_start	Whether to set window start unbounded or a specified position relative to current row, with unbounded by default	unbounded	single-select
start_position	Position from which window starts, relative to the current row (0). Negative and positive numbers indicate the number of rows that precede and follow the current row respectively.		integer
window_end	Whether to set window end unbounded or a specified position relative to current row, with unbounded by default	unbounded	single-select
end_position	Position from which window ends, relative to the current row (0). Negative and positive numbers indicate the number of rows that precede and follow the current row respectively.		integer

Examples:

For example, given the following dataset

animal	colour	date	cured	adopted
cat	blue	2019-01-01 00:00:00	false	true
cat	blue	2019-01-02 00:00:00	true	false
dog	yellow	2019-01-03 00:00:00	true	true
cat	yellow	2019-01-04 00:00:00	true	false
cat	yellow	2019-01-05 00:00:00	true	false
cat	blue	2019-01-06 00:00:00	false	false
dog	blue	2019-01-07 00:00:00	true	true
dog	blue	2019-01-08 00:00:00	false	false
dog	yellow	2019-01-09 00:00:00	false	false
dog	yellow	2019-01-10 00:00:00	false	false
cat	blue	2019-01-11 00:00:00	false	false
cat	yellow	2019-01-12 00:00:00	false	true
dog	yellow	2019-01-13 00:00:00	true	false
dog	blue	2019-01-14 00:00:00	true	false
cat	blue	2019-01-15 00:00:00	false	false
dog	yellow	2019-01-16 00:00:00	false	false
dog	yellow	2019-01-17 00:00:00	false	true
cat	blue	2019-01-18 00:00:00	false	false
cat	yellow	2019-01-19 00:00:00	false	false
dog	yellow	2019-01-20 00:00:00	false	false

with input columns cured and adopted, partitioned by animal and colour and ordered by date for indicator function all, rolling window with unbounded start and current row as end, the result is

animal	colour	date	cured	adopted	roll_cured	roll_adopted
dog	blue	2019-01-07 00:00:00	true	true	true	true
dog	blue	2019-01-08 00:00:00	false	false	false	false
dog	blue	2019-01-14 00:00:00	true	false	false	false
cat	yellow	2019-01-04 00:00:00	true	false	true	false
cat	yellow	2019-01-05 00:00:00	true	false	true	false
cat	yellow	2019-01-12 00:00:00	false	true	false	false
cat	yellow	2019-01-19 00:00:00	false	false	false	false
cat	blue	2019-01-01 00:00:00	false	true	false	true
cat	blue	2019-01-02 00:00:00	true	false	false	false
cat	blue	2019-01-06 00:00:00	false	false	false	false
cat	blue	2019-01-11 00:00:00	false	false	false	false
cat	blue	2019-01-15 00:00:00	false	false	false	false
cat	blue	2019-01-18 00:00:00	false	false	false	false
dog	yellow	2019-01-03 00:00:00	true	true	true	true
dog	yellow	2019-01-09 00:00:00	false	false	false	false
dog	yellow	2019-01-10 00:00:00	false	false	false	false
dog	yellow	2019-01-13 00:00:00	true	false	false	false
dog	yellow	2019-01-16 00:00:00	false	false	false	false
dog	yellow	2019-01-17 00:00:00	false	true	false	false
dog	yellow	2019-01-20 00:00:00	false	false	false	false

Get a DateTime value over rolling windows, with optional partitioning

Unique identifier (API): window_rolling_datetime

Please use the newer version compute_rolling_window_function

Get an aggregation function on one or more DateTime columns over rolling windows, optionally partitioned by other selected column(s).

Parameters	Parameter Description	Default	Parameter Type
aggregation_function	Aggregation function applied to input column(s)		single-select
window_start	Whether to set window start unbounded or a specified position relative to current row, with unbounded by default	unbounded	single-select
start_position	Position from which window starts, relative to the current row (0). Negative and positive numbers indicate the number of rows that precede and follow the current row respectively.		integer
window_end	Whether to set window end unbounded or a specified position relative to current row, with unbounded by default	unbounded	single-select
end_position	Position from which window ends, relative to the current row (0). Negative and positive numbers indicate the number of rows that precede and follow the current row respectively.		integer

Examples:

For example, given the following dataset

animal	time_born	time_adopted
cat	2019-01-01 00:00:00	2019-02-01 00:00:00
cat	2019-01-02 00:00:00	2019-03-01 00:00:00
cat	2019-01-03 00:00:00	2019-03-01 00:00:00
cat	2019-01-04 00:00:00	2020-04-01 00:00:00
cat	2019-01-05 00:00:00	2019-05-01 00:00:00
dog	2019-01-06 00:00:00	2020-06-01 00:00:00
dog	2019-01-07 00:00:00	2019-05-01 00:00:00
dog	2019-01-08 00:00:00	2020-07-01 00:00:00
dog	2019-01-09 00:00:00	2020-06-01 00:00:00
dog	2019-01-10 00:00:00	2019-03-01 00:00:00

the result is

animal	time_born	time_adopted	agg_time_adopted
dog	2019-01-06 00:00:00	2020-06-01 00:00:00	2020-06-01 00:00:00
dog	2019-01-07 00:00:00	2019-05-01 00:00:00	2020-06-01 00:00:00
dog	2019-01-08 00:00:00	2020-07-01 00:00:00	2020-07-01 00:00:00
dog	2019-01-09 00:00:00	2020-06-01 00:00:00	2020-07-01 00:00:00
dog	2019-01-10 00:00:00	2019-03-01 00:00:00	2020-07-01 00:00:00
cat	2019-01-01 00:00:00	2019-02-01 00:00:00	2019-02-01 00:00:00
cat	2019-01-02 00:00:00	2019-03-01 00:00:00	2019-03-01 00:00:00
cat	2019-01-03 00:00:00	2019-03-01 00:00:00	2019-03-01 00:00:00
cat	2019-01-04 00:00:00	2020-04-01 00:00:00	2020-04-01 00:00:00
cat	2019-01-05 00:00:00	2019-05-01 00:00:00	2020-04-01 00:00:00

with input column time_adopted, partitioned by animal and ordered by time_born, for aggregation function latest. The rolling window starts with unbounded and ends with current row, for example, the window of the 4th row in the input dataset

|cat   |2019-01-04 00:00:00|2020-04-01 00:00:00|

is all the rows above down to the current row

|cat   |2019-01-01 00:00:00|2019-02-01 00:00:00|
|cat   |2019-01-02 00:00:00|2019-03-01 00:00:00|
|cat   |2019-01-03 00:00:00|2019-03-01 00:00:00|
|cat   |2019-01-04 00:00:00|2020-04-01 00:00:00|

Compute a rolling numeric aggregation, with optional partitioning

Unique identifier (API): window_rolling_numeric

Please use the newer version compute_rolling_window_function

Compute an aggregation function on one or more numeric columns over rolling/expanding/shrinking windows, optionally partitioned by other selected column(s). Boolean columns are accepted as well and are converted to numeric first.

Parameters	Parameter Description	Default	Parameter Type	Parameter Min	Parameter Max	Parameter Step
agg_function	Aggregation function applied to input column(s)		single-select
relative_error	Relative error for approximating median or a specific quantile. Must be no less than 0.001 for computational efficiency.		range	0.001	1.0	0.001
quantile_to_compute	Specify which quantile to compute for the quantile rolling function. Must be a number between 0 and 1.		range	0.0	1.0	0.05
window_start	Whether to set window start unbounded or a specified position relative to current row, with unbounded by default	unbounded	single-select
start_position	Position from which window starts, relative to the current row (0). Negative and positive numbers indicate the number of rows that precede and follow the current row respectively.		integer
window_end	Whether to set window end unbounded or a specified position relative to current row, with unbounded by default	unbounded	single-select
end_position	Position from which window ends, relative to the current row (0). Negative and positive numbers indicate the number of rows that precede and follow the current row respectively.		integer

Examples:

For example, given the following dataset

colour	animal	birth	qty
blue	cat	2019-01-01 00:00:00	0.9459212999951968
yellow	dog	2019-01-02 00:00:00	0.923807614788432
yellow	dog	2019-01-03 00:00:00	0.8160265494527545
blue	cat	2019-01-04 00:00:00	0.21414080632280563
yellow	dog	2019-01-05 00:00:00	0.31936422789647023
blue	dog	2019-01-06 00:00:00	0.8090282829464444
yellow	cat	2019-01-07 00:00:00	0.23359299737078676
yellow	cat	2019-01-08 00:00:00	0.9244320791985904
blue	cat	2019-01-09 00:00:00	0.6161751606853043
blue	cat	2019-01-10 00:00:00	0.26992044210702926
yellow	dog	2019-01-11 00:00:00	0.40755657119101496
yellow	cat	2019-01-12 00:00:00	0.11657357211440877
yellow	cat	2019-01-13 00:00:00	0.8757826191122454
yellow	cat	2019-01-14 00:00:00	0.3989306451516289
blue	dog	2019-01-15 00:00:00	0.09120177599596369
blue	dog	2019-01-16 00:00:00	0.8228651830898742
yellow	cat	2019-01-17 00:00:00	0.6855467490320383
blue	cat	2019-01-18 00:00:00	0.27373326299975465
yellow	cat	2019-01-19 00:00:00	0.10849671654816584
blue	cat	2019-01-20 00:00:00	0.27289802805739094

the result is

colour	animal	birth	qty	roll_qty
yellow	dog	2019-01-02 00:00:00	0.923807614788432	0.923807614788432
yellow	dog	2019-01-03 00:00:00	0.8160265494527545	1.7398341642411865
yellow	dog	2019-01-05 00:00:00	0.31936422789647023	2.059198392137657
yellow	dog	2019-01-11 00:00:00	0.40755657119101496	2.466754963328672
blue	cat	2019-01-01 00:00:00	0.9459212999951968	0.9459212999951968
blue	cat	2019-01-04 00:00:00	0.21414080632280563	1.1600621063180023
blue	cat	2019-01-09 00:00:00	0.6161751606853043	1.7762372670033066
blue	cat	2019-01-10 00:00:00	0.26992044210702926	2.046157709110336
blue	cat	2019-01-18 00:00:00	0.27373326299975465	2.31989097211009
blue	cat	2019-01-20 00:00:00	0.27289802805739094	2.592789000167481
blue	dog	2019-01-06 00:00:00	0.8090282829464444	0.8090282829464444
blue	dog	2019-01-15 00:00:00	0.09120177599596369	0.900230058942408
blue	dog	2019-01-16 00:00:00	0.8228651830898742	1.7230952420322823
yellow	cat	2019-01-07 00:00:00	0.23359299737078676	0.23359299737078676
yellow	cat	2019-01-08 00:00:00	0.9244320791985904	1.1580250765693771
yellow	cat	2019-01-12 00:00:00	0.11657357211440877	1.274598648683786
yellow	cat	2019-01-13 00:00:00	0.8757826191122454	2.1503812677960314
yellow	cat	2019-01-14 00:00:00	0.3989306451516289	2.5493119129476605
yellow	cat	2019-01-17 00:00:00	0.6855467490320383	3.2348586619796986
yellow	cat	2019-01-19 00:00:00	0.10849671654816584	3.3433553785278645

with input column qty, partitioned by colour and animal and ordered by birth for aggregation function sum, rolling window with unbounded start and current row as end.