Moving from Python

Most of a data script reads, reshapes, filters and loads, and each of those steps has a Trinium node. This guide maps the common pandas and PySpark steps to nodes, and shows how a pipeline calls the code no node covers.

The ideas

In a script	In Trinium
The script	Pipeline: nodes wired together on a canvas. Every node runs at once and rows stream between them.
A function or a block of steps	Node.
A dataframe passed from step to step	The rows on a wire. Every node shows its output columns as you build, before a run.
`print()` and a log file	The Data, Metadata and Messages tabs under the canvas, and rows in and out for every node of every run.
The crontab	Schedules, on a timer or when files arrive.
Credentials in a config file or `.env`	Connections, encrypted and kept out of the pipeline.
Command-line arguments	Parameters: values supplied when a pipeline runs.
Git history of the script	Versions, Staging and Production.
Expressions in pandas	One SQL expression language, used in every node that takes a rule.

Step by step

Reading

Theirs	Trinium	Notes
pd.read_csv, spark.read.csv	CSV Files, S3 Files	CSV Files for files you upload; the cloud file inputs read a folder of them from S3, Azure Blob, Google Cloud Storage, SFTP or a network share.
pd.read_parquet, spark.read.parquet	Parquet Files, S3 Files	A `key=value/` folder becomes a column, and a filter on it skips the folders it rules out.
pd.read_excel	Excel Files
pd.read_json, json_normalize	JSON Files, JSON	JSON Files reads a file; JSON pulls fields out of a column that holds JSON text.
pd.read_sql, spark.read.jdbc	Postgres input, MySQL input, SQL Server input, Snowflake input	One input per database, each with a table picker or your own query. On Postgres, MySQL and SQL Server a bookmark reads only the rows past where the last successful run stopped.
requests in a loop over pages	REST API (Read)	Page, offset, cursor and next-link paging, with the credential held on the connection.

Columns

Theirs	Trinium	Notes
df[cols], rename, astype, drop	Manage Columns	Keep, rename, retype and describe columns in one place.
df.filter(regex=...), select by type	Dynamic Select, Dynamic Rename	Columns chosen by a rule, so a new column that matches is picked up without an edit.
assign, withColumn, np.where	Formula, Formula Multi-Field	Each new column is an expression; `CASE WHEN … THEN … ELSE … END` replaces `np.where`. Formula Multi-Field applies one expression to many columns.
str.strip, str.lower, str.replace	Data Cleanse, Formula, Find & Replace
str.extract, str.contains with a pattern	RegEx
str.split, explode	Text to Columns, JSON	Split into columns or into rows.
pd.to_datetime, strftime	DateTime

Rows

Theirs	Trinium	Notes
df[df.x > 0], query, where	Filter	The rows that fail the condition come out of their own anchor.
if / elif over a column, into several frames	Route	Up to ten named branches, first match wins, and the rest go to Unmatched.
sort_values, orderBy	Sort
drop_duplicates, dropDuplicates	Unique	The duplicates come out of their own anchor.
fillna with a value, the mean or the median	Fill Missing	A forward fill is Formula Multi-Row with `last_value([x]) IGNORE NULLS OVER (ORDER BY _row ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)`.
head, tail, sample	Sample
reset_index, monotonically_increasing_id	Record ID
range, date_range	Generate Rows

Combining

Theirs	Trinium	Notes
pd.merge, df.join	Join, Join Multiple	Join's three anchors are the matched rows and each side's unmatched rows; wire them into a Union for a left or full outer join.
pd.concat, unionByName	Union	By column name or by position.
pd.concat(axis=1)	Join	Join by record position.
a cross join with a small table	Append Fields
rapidfuzz, fuzzywuzzy	Fuzzy Match

Reshaping and summarizing

Theirs	Trinium	Notes
groupby().agg(), groupBy().agg()	Summarize
pivot_table, pivot	Cross Tab
melt, stack	Transpose

Row by row

Theirs	Trinium	Notes
shift, rank, cumsum, rolling, groupby().transform()	Formula Multi-Row	Window functions such as `LAG`, `RANK` and `SUM(…) OVER (PARTITION BY …)`.
a loop that carries a running value	Formula Multi-Row	Running mode: `_prev` is the previous row's result, as in `GREATEST(0, COALESCE(_prev, 0) + [received] - [shipped])`.

Checking

Theirs	Trinium	Notes
assert, pandera, great_expectations	Data Validation	Failing rows go to their own anchor, or the run stops.
df.describe, profiling	Data Profile
assert_frame_equal, df.compare	Data Compare	Every missing, extra and changed row, by key.

Writing

Theirs	Trinium	Notes
to_csv, to_parquet, to_json	CSV File, Parquet File, JSON File, S3 File
to_excel with formatting	Excel Report
to_sql, spark.write.jdbc	Postgres output, MySQL output, SQL Server output, Snowflake output	Append, replace or upsert. On Postgres, a failed load leaves no rows behind; on MySQL and SQL Server, switch on All or nothing for the same.
smtplib	Send Email	One message per row, per group or per run, with the rows attached.

Outside the pipeline

Theirs	Trinium	Notes
requests.get per row	API Lookup
a model, a library or code no node covers	API Batch Lookup	Put it behind an HTTPS endpoint of your own; API Batch Lookup sends it the rows many at a time (see below).

Code no node covers

Some steps belong in code: a trained model, a library with no equivalent, a rule your team already maintains. Keep that code where you run code today, behind a small HTTPS endpoint: a function, a container or an internal service. API Batch Lookup sends it your rows, a hundred or more to a request, and adds each row's answer as new columns.

What it sends. {"rows": [row, row, ...]}, each row an object of its column values. The field name can be changed, so an existing model endpoint works as it is.
What comes back. A list with one answer per row, in the order sent, whose fields become new columns typed as you declare them.
What the endpoint does. You say whether it only calculates (a Test run calls it, so the preview shows real answers) or changes something (a Test run sends nothing).
When a call fails. The run stops naming the rows, or keeps them with empty answers and carries on, as you choose.
Credentials. The endpoint's address and credential live on a REST API connection, which sends them to that host only.

The function itself can be a few lines. The node's help has a Python AWS Lambda to start from. To build each request from one row's values instead, such as an id in the path, use API Lookup.

A step that only reshapes data is usually shorter as nodes than as a service: try the table above first.

Function names are from the public pandas API reference and PySpark API reference. Python is a trademark of the Python Software Foundation; pandas and Apache Spark are trademarks of their owners. Trinium is not affiliated with them.