Skip to content

Clean data (prep node)

Fix decimal commas, units, timestamps, gaps, outliers and nested objects with an ordered list of operators in one node. Full operator reference and a worked example for the vibration edge CSV and a datalogger.

Updated View as Markdown

Plant data arrives messy: "0,588399" with a decimal comma, -9999 for a dead sensor, 15:30:05.250 with no date, millimetres per second where the model wants inches. The Clean data node (prep) fixes those in the flow, with no script. It holds an ordered list of steps; each step is one operator on one or more fields.

{ "id": "clean", "type": "prep", "required": ["rms", "time"], "steps": [
  { "op": "cast",    "fields": ["rms", "temp"], "to": "number" },
  { "op": "missing", "fields": "temp", "sentinels": [-9999], "fill": "last" },
  { "op": "convert", "fields": "rms", "from": "mm/s", "to": "in/s" },
  { "op": "round",   "fields": "rms", "digits": 4 }
] }

fields is a dot-path or a list of them. * matches any run of characters and ? one character in a name: metadata.*, vib_?, axes.*.position. A glob selects what exists; a plain path that is absent is simply skipped (except by missing, which can create it). A mistake in a step is refused at deploy with the step number: step 3 (convert): unknown unit 'furlong'. A misspelled option is refused too.

Machine records and free JSON

The node works on both record shapes.

  • A machine record (the canonical CanonicalMachineData with machine, status, metadata…) is strict: an unknown top-level field makes a later Filter or Branch reject it. So on a machine record every path whose first segment is not a machine key (source, timestamp, machine, status, axes, spindle, quality, valueQuality, metadata) or an engine _ key is read and written under metadata: rms means metadata.rms, a new field temp_f lands in metadata.temp_f. Machine keys can still be addressed (status.state). If a step would leave the machine record invalid (a number in status.state), the record fails with that reason instead of travelling on broken. select keeps the machine keys, drop refuses them.
  • Any other JSON object (webhook body, CSV row, ingest record) is addressed as written.

Engine keys (_hook, _schedule…) are never touched by flatten, unflatten or select.

Operators

All steps accept the options below; unlisted options do not exist. dest writes the result to another path and keeps the source; it needs exactly one plain field.

Fields

op Options What it does
select fields Keep only these fields (plus engine keys and, on a machine record, its machine keys).
drop fields Remove fields.
rename from, to, map from → to. One * in the last segment of both carries the match: vib_* → v_*_mm. map is { "old": "new" }.
move fields, to Move fields into the object at to, keeping their key names.
flatten path, sep (_), depth Nested objects become a_b keys. path default: metadata of a machine record, else the whole record. Arrays stay.
unflatten path, sep a_b keys become nested objects. A key whose prefix is already a scalar stays flat.

Types: cast

{ "op": "cast", "fields": ..., "to": "number" | "integer" | "boolean" | "string" | "timestamp" }

Option Applies to Meaning
on_error all null (default), keep the old value, or fail the record with the field and reason.
decimal number, integer auto (default), comma or point.
percent number, integer number ("45%" → 45, default) or fraction (0.45).
rounding integer error (default: 1.5 is refused), round, floor, ceil, trunc.
true_values, false_values boolean Extra words, case-insensitive.
format timestamp strftime format or list, tried in order.
unit timestamp Epoch unit of numbers: auto (default), s, ms, us.
tz timestamp IANA zone of text without an offset (default UTC).
date, date_field, date_format timestamp The day of time-only text: a literal YYYY-MM-DD, or a field and its format. Default: the day of the record’s timestamp, else today.
out, out_format timestamp Output: rfc3339 (default, UTC), epoch_s, epoch_ms, epoch_us, or any strftime out_format in tz.

Numbers. A string is read tolerantly: surrounding space and a currency sign are ignored, a unit suffix is dropped (12.5 mm/s → 12.5, it is not converted), % is recognised, an exponent works (1.5e3).

Text Result Why
0,588399 0.588399 one comma = decimal comma
1.234,5 1234.5 both: the last separator is the decimal one
1,234.5 1234.5 same
1,234,567 / 1.234.567 1234567 repeated = thousands
1 234 567, 1'234 1234567, 1234 space, ', _ between groups of three digits
12.5 mm/s, 3,2bar 12.5, 3.2 unit dropped
Rp 1.250.000 1250000 currency sign
1,234 1.234 one comma is decimal in auto; set decimal: "point" for a thousands comma
abc, NaN, 12 34 apples error not a number

A blank string casts to null (not an error). A boolean casts to 1 or 0. Booleans: true/false, yes/no, y/n, t/f, on/off, 1/0, plus ya/tidak, ja/nein, oui/non; the numbers 0 and 1. Arrays are cast element by element (a spectrum of "0,5" strings), with on_error applied per element.

Timestamps. Text with an explicit offset keeps it (+02:00 wins over tz). Zone-less text is read in tz (a clock change that makes a time not exist is an error; a repeated hour takes the first). Without format the node reads ISO 8601, YYYY-MM-DD hh:mm:ss.fff, YYYY-MM-DD, hh:mm[:ss.fff] and epochs. An epoch with unit: auto is guessed by size: below 1e11 seconds, below 1e14 milliseconds, below 1e17 microseconds.

Text

op Options What it does
trim fields, chars, dest Trim whitespace, or the listed characters.
case fields, mode (lower, upper, title), dest Change case.
replace fields, pattern, with, literal, dest Regex replace-all; $1 and ${name} in with. literal matches plain text.
extract fields, pattern, into Each named group (?P<name>...) becomes a string field, next to the source or under into. No match: nothing written, counted as an error.
split fields, sep or pattern, trim (true), drop_empty, dest Text → array.
join fields, sep, dest Array → text. With dest, several fields (arrays or scalars) are joined into one.

Patterns are Rust regexes (linear time, no look-around), size-limited. A bad pattern is refused at deploy.

Missing values: missing

A value is missing when it is absent, null, empty or blank text (empty: false turns that off), or equal to a sentinel. sentinels is a list: numbers match by value and also as text (-9999 matches "-9999,0"), text matches case-insensitively ("NaN", "n/a"). A missing value is then filled by fill:

fill Meaning
null (default) Only normalise: missing becomes null.
const The value (a number, text, boolean or null). Also creates a plain-path field that is absent.
last The last good value of the same series.
mean, median Of the last window good numeric values of the series (default 20, max 10000). Numeric text counts.
interpolate Linear interpolation between the nearest good numbers inside an array field. Leading and trailing gaps have no neighbour on both sides and stay null. A scalar has no neighbours inside one record, so it stays missing (a stream cannot see its next value).

For an array field every mode works element by element: const fills each gap, last carries forward inside the array, mean and median use the array’s own good values.

A series is the key template (default: the record’s machine id) plus the field path. State is per series and step, in memory, bounded: window values per series and max_series series per step (default 1000, the oldest is forgotten). After a restart or redeploy the state is empty: the first records of a series find nothing to fill from and stay null, which the report counts as unfilled. Nothing wrong is ever invented. Order is arrival order: a late or out-of-order record is treated as the newest. Only good values update the state, so a filled value is never remembered as if it had been measured.

Numbers

op Options What it does
clamp fields, min, max Limit to a range.
range fields, min, max, action (null or flag), flag Outside the range: set null, or keep it and add the field’s path to the array at flag (default out_of_range).
round fields, digits (0; negative allowed), mode (half_up, half_even, floor, ceil, trunc), dest Round. 0.285 at 2 digits is 0.29 (binary noise is removed first).
scale fields, factor, offset, dest value × factor + offset.
convert fields, from, to, dest Unit conversion from the built-in table.

Numeric operators work on numbers and element by element on arrays; text is left alone and counted as an error (cast first). scale and convert round their result to 12 significant digits so 100 °C → °F is 212, not 212.00000000000003.

Units (case-insensitive; °C, m/s², µm spellings work). Both units must be of one kind:

Kind Units
acceleration g, mg, m/s2, mm/s2, in/s2, gal
velocity mm/s, in/s (ips), m/s, um/s, mil/s, ft/s, km/h
length m, cm, mm, um, mil, in, ft, km
temperature C, F, K
pressure Pa, kPa, MPa, bar, mbar, psi, atm, mmHg, kg/cm2
frequency Hz, kHz, MHz, rpm (cpm), rad/s
time us, ms, s, min, h, d

Groups

op Options What it does
bin fields, edges, labels, closed (left/right), outside, dest Fixed edges, ascending. n edges with n-1 labels are bounded bins (the outer edge belongs to the end bin); with n+1 labels the outer bins are open. Outside: null or the outside label.
recode fields, table, unmapped (keep, null, default), default, ignore_case, dest Map values through an inline table. Numbers match by their plain text (1, 1.5). For tables shared between flows use the Lookup node.

Derive and arrays

op Options What it does
derive dest, expr, on_error A new field from a native DSL expression: metadata.temp * 1.8 + 32, metadata.rms > 5, metadata.a + "-" + metadata.b. Fields are read as metadata.<name> on both record shapes; the DSL has no functions. Division by zero or maths on text is an error (on_error).
dedupe_array fields, by Keep the first of equal array elements; by compares objects on one sub-path. 1 and 1.0 are equal.

Record level

  • required: plain paths that must hold a value after the last step (present, not null, not blank). A record without one is dropped (on_required: "drop", the default, counted as a drop) or failed ("fail", to the error port or the dead-letter queue).
  • report (default true): adds a top-level _prep object, which a machine-record Filter ignores like any engine key. Set "report": false to leave records untouched.
"_prep": {
  "changed": 9, "nulled": 1, "filled": 1, "errors": 1,
  "steps": [ { "step": 2, "op": "cast", "changed": 5, "errors": 1 }, { "step": 7, "op": "missing", "changed": 1 } ],
  "fields": { "rms": ["1:rename", "4:cast", "9:convert"], "temp": ["4:cast", "7:missing"] }
}

steps lists the steps that did something (1-based). fields is the lineage: which steps changed which field, in order (first 64 fields, 8 steps each). The flow trace shows N changes in M steps, E errors per hop.

Worked example: the vibration edge CSV and a datalogger

The edge writes one row per reading. The row has a German time column with fractions and no date, a European decimal comma in a quoted cell, -9999 for a dead channel and NaN for a dropped temperature:

{ "RMS (mm/s)": "0,588399", "Temp [C]": " 45,5 ", "Uhrzeit": "15:30:05.250", "Datum": "02.11.2026", "peak_x": "1.234,5" }
{ "RMS (mm/s)": "-9999",    "Temp [C]": "NaN",    "Uhrzeit": "15:30:06.250", "Datum": "02.11.2026", "peak_x": "" }
{ "RMS (mm/s)": "12,7",     "Temp [C]": "--",     "Uhrzeit": "15:30:07.000", "Datum": "02.11.2026", "peak_x": "2" }

One node cleans it (this is the plant-clean-edge-csv example in the gallery):

{ "id": "clean", "type": "prep", "required": ["rms", "time"], "steps": [
  { "op": "rename", "map": { "RMS (mm/s)": "rms", "Temp [C]": "temp", "Uhrzeit": "time", "Datum": "date" } },
  { "op": "trim", "fields": "*" },
  { "op": "missing", "fields": "*", "sentinels": ["-9999", "NaN", "--"] },
  { "op": "cast", "fields": ["rms", "temp", "peak*"], "to": "number" },
  { "op": "cast", "fields": "time", "to": "timestamp", "format": "%H:%M:%S%.f",
    "date_field": "date", "date_format": "%d.%m.%Y", "tz": "Asia/Jakarta" },
  { "op": "drop", "fields": "date" },
  { "op": "missing", "fields": "temp", "fill": "last" },
  { "op": "range", "fields": "rms", "min": 0, "max": 50 },
  { "op": "convert", "fields": "rms", "from": "mm/s", "to": "in/s" },
  { "op": "round", "fields": "rms", "digits": 4 },
  { "op": "bin", "fields": "temp", "edges": [0, 40, 70, 120], "labels": ["cool", "ok", "hot"], "dest": "band" }
] }
Row Result
1 { "rms": 0.0232, "temp": 45.5, "time": "2026-11-02T08:30:05.250Z", "peak_x": 1234.5, "band": "ok" }: 15:30 Jakarta is 08:30 UTC; 0.588399 mm/s is 0.0232 in/s.
2 Dropped: -9999 is a sentinel, so rms is missing and required fails. The temp gap was filled with the last good value (45.5) but the record does not survive.
3 { "rms": 0.5, "temp": 45.5, "time": "2026-11-02T08:30:07Z", "peak_x": 2, "band": "ok" }: the -- temperature is taken from the last good reading.

The same steps work on a machine record: the fields then live under metadata and a Filter after the node (metadata.rms > 0.01) keeps working.

Shaping the row into a machine record

The hub, storage and push sinks take machine records. The gallery example therefore ends the step list by building one out of the cleaned row (rows are not machine records, so every path is addressed as written until machine exists):

{ "op": "derive", "dest": "timestamp", "expr": "metadata.time" },
{ "op": "derive", "dest": "source", "expr": "\"edge-csv\"" },
{ "op": "derive", "dest": "status.state", "expr": "\"running\"" },
{ "op": "move", "fields": ["rms", "temp", "peak_x", "band", "time"], "to": "metadata" },
{ "op": "derive", "dest": "machine.id", "expr": "\"edge-1\"" },
{ "op": "select", "fields": "metadata.*" }

derive reads a plain row as metadata.<field>, so metadata.time is the time column. Once machine appears the record is a machine record: the node checks it against the machine schema and fails with the reason if something is still wrong, and required and the later steps address plain names under metadata. select drops every stray top-level column (it keeps the machine keys and engine keys).

Limits

  • At most 200 steps per node; a step list is evaluated in order for every record, so keep globs narrow on wide records.
  • derive reads the record through the machine view, which copies it once per derive step.
  • Paths are dot-paths: a key that contains a dot cannot be addressed (flatten refuses . as separator for that reason). Rename such keys first, with rename on their parent.
  • The node is deterministic: no clock is read except for time-only text with no date, date_field or record timestamp.
Navigation

Type to search…

↑↓ navigate↵ selectEsc close