Dataset Aggregate, Group By & Pivot

Just scraped 5,000 rows and now you need the summary, not the rows? Orders per region, average price per brand, listings per city per month, top 10 products by revenue? This Actor is SQL GROUP BY and a spreadsheet pivot table for any Apify dataset or JSON array. Point it at your data, say what to group by and what to compute, and get back a clean summary table plus a ready-to-open CSV or Excel file.

No scraping, no API keys, no browser. It only processes data you already have, so there is nothing to break and nothing to maintain.

What it does

• Group by one or several fields (like SQL GROUP BY), including nested fields via dot paths (address.city). Leave the group fields empty to summarize the whole dataset into a single row.
• 11 aggregation functions: count, countDistinct, sum, avg, min, max, median, first, last, list, listDistinct. As many per run as you want, each with its own output column name.
• Date bucketing: group a date or timestamp field by day, ISO week, month, quarter or year (orderedAt becomes orderedAt_month = 2026-08). Accepts ISO dates, common date strings, and Unix timestamps in seconds or milliseconds.
• Pivot tables: turn one field's distinct values into columns. Group by region, pivot on product, fill the cells with sum of amount, and you get one row per region with a column per product, zero-filled where a combination has no rows.
• Lenient numbers (on by default): sums and averages read "$1,234.50", "49 USD", "12%" and "(300)" as numbers, which is what scraped prices usually look like. Values that genuinely are not numbers ("n/a") are skipped and honestly counted in the run summary, never guessed.
• Normalized grouping (on by default): South, south and SOUTH land in the same group, with one consistent label in the output. Switch to exact matching when byte-for-byte distinction matters.
• Sort, top N, totals: sort by any output column, keep only the top N groups (top 10 products by revenue), and add a grand-total row covering every input row.
• Real file export: a ready-to-open CSV and/or Excel (.xlsx) file with a bold, frozen header row, saved to the run's key-value store.
• A summary report (AGGREGATE_SUMMARY): rows in, groups out, skipped values per column, and explicit warnings for things like a misspelled field name, so a typo never silently produces an empty result.

Example

Input rows (from any scraper, or pasted inline):

[
  { "region": "North", "product": "Widget", "amount": "$1,200.00", "orderedAt": "2026-07-03" },
  { "region": "North", "product": "Gadget", "amount": 350, "orderedAt": "2026-07-18" },
  { "region": "south", "product": "Gizmo", "amount": 120, "orderedAt": "2026-08-02" }
]

Group by region, count orders, sum and average amount:

region	orders	total_amount	avg_amount
North	2	1550	775
South	1	120	120

Or group by region, pivot on product with sum of amount:

region	orders	Gadget	Gizmo	Widget
North	2	350	0	1200
South	1	0	120	0

How to use it

1. Point Dataset to aggregate at any existing dataset on your account (any scraper run's output), or paste rows into Data (inline).
2. Set Group by field(s), e.g. region, or city + category.
3. Set Aggregations, e.g. [{"field": "price", "function": "avg", "alias": "avg_price"}]. Leave empty for a plain row count per group.
4. Optionally set a date bucket, a pivot field, sort, top N, a totals row, and export formats.
5. Run. Read the summary table in the dataset tab, download the CSV/Excel from the key-value store.

Works the same from the API and from AI agents via MCP: the input is plain JSON, the output is a plain dataset.

Pricing

• $0.001 per input row processed (the primary event). Charged per row going in, not per group coming out, so a 5,000-row dataset costs the same $5.00 whether it collapses into 5 groups or 500.
• $0.01 per exported file (CSV or Excel).
• Concretely: summarizing a 1,000-row scrape with both a CSV and an Excel download costs about $1.02. A weekly 500-row report with one CSV is about $0.51 per week.

Works with the rest of the Nero Labs dataset toolkit

Five small tools that chain together, all pay-per-event, none of them scrape anything:

• Dataset Cleaner & Exporter: dedupe (exact, normalized or fuzzy), flatten nested JSON, clean emails/phones/URLs, export.
• Dataset Filter & Transform: filter rows by rule, reshape fields (dates, replace, split, hash, 25 ops), sort, dedupe, limit; also reads CSV/Excel/JSON files and Google Sheets by URL.
• Dataset Join & Merge: VLOOKUP-style joins and unions across two datasets on a key field.
• Dataset Aggregate, Group By & Pivot (this one).
• Dataset Diff & Change Detector: what was added, removed or changed between two runs.

A common pipeline: scraper, then Cleaner, then Filter & Transform (appending to a named dataset), then Join to enrich, then Aggregate for the weekly summary, with Diff watching what changed since last time.

FAQ

My numbers are text, like "$1,234.50" or "49 USD". Will sum and average work? Yes, that is the default. Lenient number parsing handles currency symbols, thousands separators (both 1,234.56 and European 1.234,56), percent signs and accounting negatives like (300). Anything that genuinely is not a number is skipped and counted in the run summary, never silently treated as zero.

What happens to rows where the group field is empty or missing? They are grouped together under an explicit (blank) label, so they stay visible instead of disappearing. The summary also warns you if none of your rows has the field at all, which usually means a typo in the field name.

Can I get one row per month from a messy date field? Yes. Set the date bucket field to your date column and pick day, week, month, quarter or year. Unreadable dates land in an explicit (invalid date) group rather than being dropped.

Does this modify my original dataset? No. The source dataset is only read. The result goes to this run's own dataset and key-value store.

Is there a size limit? There is a hard safety ceiling of 200,000 input rows per run, and you can set your own lower cap with Maximum input rows as a cost guard.

If this Actor saved you a spreadsheet pivot session or a one-off GROUP BY script, a review on this page genuinely helps a small tool get found. If something did not work, open an issue in the Issues tab and you will get a personal reply.