Dataset Transform: Rename, Flatten, Filter & Sort Columns

Dataset transform tool: run an ordered pipeline of steps on an Apify dataset, a CSV, JSON, JSONL or Excel file, or rows you paste. Rename columns, flatten JSON, filter rows, sort, sample, split, explode lists and add computed columns. The pipeline is plain JSON, so no code runs and nothing needs a script.

What you get

• Transformed rows in the run's dataset, plus an optional CSV, Excel, JSON or JSONL file with a download link.
• A SUMMARY record: rows in and out, rows in and out of every step, value problems with examples, source errors.
• An early, plain-language error for a bad pipeline, before any row is charged.

What it does not do: it does not merge or join several sources by a key (it only reads them one after another; see Related tools), does not run your own code or scripts, does not fetch web pages, and does not do fuzzy matching. Sorting and random sampling hold the rows in memory.

How to transform a dataset

1. Put your rows in one of the sources: pick Apify datasets, give file links, or paste items.
2. Describe the steps in operations, top to bottom.
3. Run. Optionally ask for csv or xlsx in exports.

Input example (copy and paste)

{
  "items": [
    {"id": 3, "name": " Wool Socks ", "price": "24", "stock": 150, "tags": ["wool"], "seller": {"name": "Nordic Gear", "country": "NO"}, "created": "2025-02-20T08:15:00Z"},
    {"id": 6, "name": "Rain Jacket", "price": "129", "stock": 5, "tags": ["clothing"], "seller": {"name": "Alpine Ltd", "country": "CH"}, "created": "2025-02-05T07:20:00Z"}
  ],
  "operations": [
    {"op": "flatten", "delimiter": "_", "arrays": "join"},
    {"op": "text", "fields": ["name"], "action": "trim"},
    {"op": "cast", "types": {"price": "number"}},
    {"op": "rename", "mapping": {"seller_name": "seller", "seller_country": "country"}},
    {"op": "filter", "where": {"field": "stock", "op": "gt", "value": 0}},
    {"op": "add_column", "column": "price_with_tax", "expr": "round(price * 1.2, 2)"},
    {"op": "sort", "by": ["price:desc"]}
  ],
  "exports": ["csv"]
}

Output example (from a real local run of the prefill)

{
  "id": 6,
  "name": "Rain Jacket",
  "price": 129,
  "price_with_tax": 154.8,
  "seller": "Alpine Ltd",
  "country": "CH",
  "tags": "clothing",
  "year": 2025
}

Before and after (three real local runs)

1. Flatten nested JSON and rename headers. Steps: flatten (delimiter _, arrays by index), rename with style snake_case, drop empty values.

Before:

{"id": "u1", "User Name": "Ann Lee", "profile": {"email": "ann@example.com", "address": {"city": "Oslo", "zip": "0150"}}, "orders": [{"sku": "A1", "qty": 2}, {"sku": "B7", "qty": 1}]}

After:

{"id": "u1", "name": "Ann Lee", "profile_email": "ann@example.com", "profile_address_city": "Oslo", "orders_0_sku": "A1", "orders_0_qty": 2, "orders_1_sku": "B7", "orders_1_qty": 1}

2. Filter with AND/OR rules, sort naturally, number the rows, compute years. Six CSV rows in, five out (Room 10 sorts after Room 9, not after Room 1).

Before (first CSV row):

{"employee": "Dana Cole", "room": "Room 10", "department": "Sales", "salary": 52000, "start": "2023-04-01"}

After (first two output rows):

[{"row": 1, "employee": "Fay Ross", "room": "Room 1", "department": "Sales", "salary": 61000, "years": 4},
 {"row": 2, "employee": "Eli Park", "room": "Room 2", "department": "Support", "salary": 41000, "years": 2.3}]

3. Split, fill, cast and explode. Three orders in, five rows out (one per item). The text n/a in total could not become a number: that value became null and the run's SUMMARY lists it (column 'total' to number: 'n/a' is not a number).

Before:

{"order": "1001", "customer": "  Zoe Adams ", "city": "Austin", "items": "pen;ink;pad", "total": "19.50", "paid": "yes"}

After (first two rows):

[{"order": 1001, "city": "Austin", "items": "pen", "total": 19.5, "paid": true, "first_name": "Zoe", "last_name": "Adams", "line": 0, "order_label": "ZOE-1001"},
 {"order": 1001, "city": "Austin", "items": "ink", "total": 19.5, "paid": true, "first_name": "Zoe", "last_name": "Adams", "line": 1, "order_label": "ZOE-1001"}]

How to rename columns

{"op": "rename", "mapping": {"User Name": "name", "address.city": "city"}, "style": "snake"}

mapping renames chosen columns (a dotted nested path becomes a top-level column). style (snake, kebab, camel, pascal, lower, upper, title) restyles all other columns; prefix and suffix are also available. Use reorder ({"op": "reorder", "fields": ["id", "name"], "rest": "alphabetical"}) to change the order, select to keep columns ({"field": "b.c", "as": "c"} renames while selecting, * is a wildcard) and drop to remove them.

How to flatten JSON

{"op": "flatten", "delimiter": "_", "arrays": "index", "maxDepth": 10}

Nested objects become parent_child columns. arrays is keep (default, leave lists), join (scalars joined with joinWith, default , ) or index (orders_0_sku; lists longer than maxArrayItems, default 50, are kept as lists and noted in the SUMMARY). unflatten does the reverse, turning a.b.0 columns back into nested objects and lists. To get one row per list item use explode.

How to filter a dataset

{"op": "filter", "where": {"any": [
  {"field": "department", "op": "equals", "value": "Sales"},
  {"all": [{"field": "salary", "op": ">", "value": 40000}, {"field": "department", "op": "in", "value": ["Support", "Legal"]}]}
]}}

Conditions: equals, notEquals, contains, notContains, startsWith, endsWith, regex, gt (>), gte, lt (<), lte, between ([min, max]), in (list), exists, notExists, empty (is empty), notEmpty. Combine with all, any and not, nested up to 8 levels. "action": "remove" deletes the matching rows instead of keeping them. Matching ignores case unless "caseSensitive": true.

All steps

Step	What it does
select, drop, rename, reorder	Choose, remove, rename and order columns
flatten, unflatten	Nested objects and lists to columns and back
filter	Keep or remove rows by conditions
sort	Multi-key; natural: true for Room 2 before Room 10; numbers, ISO dates and text; empty values last
sample	first N, random N or fraction (with seed, default 42), every Nth (with offset)
limit, skip	First N rows, skip N rows
add_column	expr, a constant value, or rowNumber (start, step)
text	trim, lower, upper, title, collapse_spaces, replace, regex_replace on chosen columns
cast	string, integer, number, boolean, date, datetime, json
fill	Empty cells to a constant, or the value from the row above (previous)
split	One column into several by a delimiter (into names or maxParts)
explode	A list (or delimited text) into one row per item; several columns can be zipped

Unknown steps or settings are rejected before any row is charged, with a hint such as Did you mean 'sort'?.

Computed columns: the expression language

Examples: price * qty, first & " " & last, {Unit Price} * 1.2 (braces for names with spaces), if(stock > 0, "yes", "no"), date_part(created, "year"), round(a / b, 2). Operators + - * / % **, & (text join), comparisons, and, or, not. Functions: concat, upper, lower, trim, title, slug, collapse_spaces, replace, regex_replace, regex_extract, substr, left, right, split_part, len, join, contains, starts_with, ends_with, pad_left, number, text, round, abs, floor, ceil, sqrt, min, max, sum, coalesce, if, is_empty, date_part (year, month, day, quarter, week, weekday, weekdayname, date, epoch ...), date_format, days_between, add_days. This is a hand-written parser with a fixed list of functions. Python's eval is never used, there is no attribute access, no imports and no loops.

Inputs

Field	Default	Meaning
operations (required)	-	The ordered steps
items	-	Pasted JSON rows
datasetIds, datasetUrls	-	Apify datasets (picker, IDs, links)
fileUrls	-	CSV, TSV, JSON, JSONL, XLSX links
inlineSources	-	Pasted CSV/JSON/JSONL text
onError	null	A value that cannot be computed or cast becomes null, or dropRow
exports	none	csv, xlsx, json, jsonl
exportName	transformed	File base name
escapeFormulas	true	Protect CSV from =/+/-/@ formula injection
maxItems	100000	Input rows read, across all sources
maxOutputRows	1000000	Safety cap for explode
maxFileSizeMb	50	File download cap
csvInferTypes	true	Numbers and booleans from CSV text
xlsxSheet	first	Sheet name or number

How much does it cost

Pay per event: $0.0017 per input row (row-transformed, $1.70 per 1,000) and $0.004 per export file (export-file). You pay for rows read, not rows written, so a filter or explode does not change the price. Rows are charged in chunks of up to 500 (50 when the pipeline has a limit or sample step) before they are processed; if your spending limit runs out the run stops with what was paid for.

Job	Cost
1,000 rows, dataset only	$1.70
10,000 rows + CSV	$17.004
100,000 rows + CSV and Excel	$170.008

An invalid pipeline or an empty source costs nothing.

Use cases

• Flatten scraper output (nested JSON) into spreadsheet columns before export.
• Rename and restyle columns so two scrapers' datasets use the same field names.
• Keep only the rows you need (price range, country list, text patterns) and sort them.
• Take a reproducible random sample or every Nth row for QA.
• Split "Last, First" columns, explode tag lists into rows, cast text to numbers and dates.

FAQ

Does it run code? No. The pipeline is JSON; expressions go through a fixed parser and function list.

What if one value is bad? By default it becomes null and is counted in SUMMARY.valueErrors with up to five examples; set onError to dropRow to drop such rows. A bad source never stops the other sources.

How big can it go? Streaming steps use little memory. sort and random sample hold the surviving rows in memory (bounded by maxItems). A JSON array file is limited to 100 MB; use JSONL or CSV for bigger files.

Does a limit step save money? Yes. When a limit or first-N sample is full and no sort or random sample comes before it, reading stops. Up to 49 extra rows may have been charged already.

Can it read a dataset from another account? Only if your run can read it; pick it in Apify datasets. Links to localhost and private networks are blocked for file URLs.

Does it join two datasets? No. Several sources are processed one after another through the same pipeline.

Use with the Apify API, MCP, n8n and Make

Python:

from apify_client import ApifyClient
run = ApifyClient("<TOKEN>").actor("<username>/dataset-transform").call(run_input={
    "datasetIds": ["<DATASET_ID>"],
    "operations": [{"op": "flatten", "delimiter": "_"}, {"op": "sort", "by": ["price:desc"]}],
})

JavaScript:

import { ApifyClient } from 'apify-client';
const run = await new ApifyClient({ token: '<TOKEN>' }).actor('<username>/dataset-transform').call({
  items: [{ a: { b: 1 } }],
  operations: [{ op: 'flatten', delimiter: '_' }],
});

In n8n and Make use the Apify node/module "Run Actor" with the same JSON input. MCP clients can call it through the Apify MCP server; the only required field is operations.

Related tools

• Merge Datasets & Dedupe: Combine, Filter, Export CSV & Excel
• Remove Duplicates: Deduplicate Datasets, CSV & Excel
• Excel to JSON Converter: XLSX, XLS & CSV to JSON API