# Dataset Aggregate, Group By & Pivot (`nerolabs/dataset-aggregate-pivot`) Actor

Returns GROUP BY and pivot tables for any Apify dataset or JSON array: count, sum, average, min, max, median per group, date buckets, plus CSV or Excel export. Inputs: dataset ID or inline data, group-by fields, aggregations. Agent-ready: pay per event (x402 and MCP), per input row and file.

- **URL**: https://apify.com/nerolabs/dataset-aggregate-pivot.md
- **Developed by:** [Adam Pearce](https://apify.com/nerolabs) (community)
- **Categories:** Developer tools, Automation, Agents
- **Stats:** 2 total users, 1 monthly users, 100.0% runs succeeded, 0 bookmarks
- **User rating**: No ratings yet

## Pricing

from $1.00 / 1,000 input row processeds

This Actor is paid per event. You are not charged for the Apify platform usage, but only a fixed price for specific events.

Learn more: https://docs.apify.com/actors/running/actors-in-store.md#pay-per-event

## What's an Apify Actor?

Actors are web data automations that power AI and operations. They run on the Apify platform to scrape websites, process data, connect APIs, and automate workflows.
In Batch mode, an Actor accepts a well-defined JSON input, performs an action which can take anything from a few seconds to a few hours,
and optionally produces a well-defined JSON output, datasets with results, or files in key-value store.
In Standby mode, an Actor provides a web server which can be used as a website, API, or an MCP server.
Actors are written with capital "A".

## How to integrate an Actor?

If asked about integration, you help developers integrate Actors into their projects.
You adapt to their stack and deliver integrations that are safe, well-documented, and production-ready.
The best way to integrate Actors is as follows.

- **AI agents and MCP clients** — the [Apify MCP server](https://docs.apify.com/integrations/mcp.md) at `https://mcp.apify.com` (remote, streamable HTTP, OAuth on first use).
- **Agentic workflows and local Actor development** — [Agent Skills](https://apify.com/.well-known/agent-skills/index.json) with the [Apify CLI](https://docs.apify.com/cli/docs.md): `npm install -g apify-cli`, then `apify login`.
- **JavaScript/TypeScript projects** — the official [JS/TS client](https://docs.apify.com/api/client/js/docs.md): `npm install apify-client`.
- **Python projects** — the official [Python client](https://docs.apify.com/api/client/python/docs.md): `pip install apify-client`.
- **Any other language** — the [REST API](https://docs.apify.com/api/v2.md).

For usage examples, see the [API](#api) section below.

For more details, see Apify documentation as [Markdown index](https://docs.apify.com/llms.txt) and [Markdown full-text](https://docs.apify.com/llms-full.txt).

# README

## 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):

```json
[
  { "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](https://apify.com/nerolabs/dataset-cleaner-exporter): dedupe (exact, normalized or fuzzy), flatten nested JSON, clean emails/phones/URLs, export.
- [Dataset Filter & Transform](https://apify.com/nerolabs/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](https://apify.com/nerolabs/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](https://apify.com/nerolabs/dataset-diff-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.

# Actor input Schema

## `datasetId` (type: `string`):

Pick an existing Apify dataset (for example the output of any scraper run). Use this OR 'Data (inline)' below, not both. Declaring it this way is what lets this Actor run with limited permissions: it may read the dataset you point at, and nothing else on your account.

## `data` (type: `array`):

A JSON array of records to aggregate, for ad-hoc data instead of a dataset ID.

## `groupByFields` (type: `array`):

One output row per distinct combination of these field values (like SQL GROUP BY). Leave empty to aggregate the whole dataset into a single row. Nested fields work with dot paths, e.g. 'address.city'.

## `aggregations` (type: `array`):

What to compute per group. Each item: {"field": "amount", "function": "sum", "alias": "total\_amount"}. Functions: count (rows, or non-blank values of a field), countDistinct, sum, avg, min, max, median, first, last, list (all values joined by commas), listDistinct. 'alias' is the output column name (optional, defaults to function\_field). If empty, a plain row count per group is produced.

## `groupMatching` (type: `string`):

'Normalized' treats 'South', 'south ' and 'SOUTH' as the same group (case-insensitive, surrounding and repeated whitespace ignored), which is what scraped or hand-entered data usually needs. 'Exact' requires byte-for-byte identical values.

## `dateBucketField` (type: `string`):

A date or timestamp field to group by time period, e.g. 'orderedAt' or 'createdAt'. A derived column like 'orderedAt\_month' is added to the group-by fields automatically. Accepts ISO dates, most common date strings, and Unix timestamps in seconds or milliseconds.

## `dateBucketGranularity` (type: `string`):

How to bucket the date field: by calendar day (2026-08-19), ISO week (2026-W34), month (2026-08), quarter (2026-Q3) or year (2026).

## `pivotField` (type: `string`):

Turn this field's distinct values into columns, spreadsheet pivot-table style. For example group by 'region' and pivot on 'product' to get one row per region with a 'Widget', 'Gadget', 'Gizmo' column each. Cannot also be a group-by field.

## `pivotValueField` (type: `string`):

The field whose values fill the pivot cells (e.g. 'amount'). Leave empty to fill each pivot cell with a row count.

## `pivotFunction` (type: `string`):

How to combine the pivot value field within each cell. Ignored (row count used) when no pivot value field is set.

## `lenientNumbers` (type: `boolean`):

Read numbers stored as text, like '$1,234.50', '49 USD', '12%' or '(300)', as numbers for sum/avg/min/max/median. Scraped prices almost always need this. Turn off to only accept real numbers and plain numeric strings.

## `sortBy` (type: `string`):

An output column to sort the groups by: a group-by field, an aggregation alias (e.g. 'total\_amount'), or a pivot column. Leave empty to sort by the group-by fields.

## `sortDirection` (type: `string`):

Ascending (A to Z, smallest first) or descending (largest first, e.g. biggest revenue at the top).

## `topN` (type: `integer`):

After sorting, keep only the first N groups (e.g. top 10 products by revenue). Leave empty to keep all groups. The totals row, if requested, still covers every input row, not just the kept groups.

## `includeTotalsRow` (type: `boolean`):

Append one extra row aggregating every input row, labelled '(total)' in the first group-by column, with a '\_rowType' column marking 'group' vs 'total' rows.

## `exportFormats` (type: `array`):

Also save the result as a real downloadable file in the run's key-value store. CSV opens anywhere; XLSX opens in Excel and Google Sheets with a bold, frozen header row.

## `maxItems` (type: `integer`):

Stop loading after this many rows from the dataset (a cost guard for large datasets). There is a hard safety ceiling of 200,000 rows per run regardless.

## Actor input object example

```json
{
  "data": [
    {
      "orderId": 1001,
      "region": "North",
      "product": "Widget",
      "amount": "$1,200.00",
      "orderedAt": "2026-07-03"
    },
    {
      "orderId": 1002,
      "region": "North",
      "product": "Gadget",
      "amount": 350,
      "orderedAt": "2026-07-18"
    },
    {
      "orderId": 1003,
      "region": "South",
      "product": "Widget",
      "amount": "890.50",
      "orderedAt": "2026-07-22"
    },
    {
      "orderId": 1004,
      "region": "south",
      "product": "Gizmo",
      "amount": 120,
      "orderedAt": "2026-08-02"
    },
    {
      "orderId": 1005,
      "region": "East",
      "product": "Widget",
      "amount": 2400,
      "orderedAt": "2026-08-05"
    },
    {
      "orderId": 1006,
      "region": "East",
      "product": "Gadget",
      "amount": "n/a",
      "orderedAt": "2026-08-09"
    },
    {
      "orderId": 1007,
      "region": "North",
      "product": "Gizmo",
      "amount": 75,
      "orderedAt": "2026-08-11"
    },
    {
      "orderId": 1008,
      "region": "",
      "product": "Widget",
      "amount": 410,
      "orderedAt": "2026-08-14"
    }
  ],
  "groupByFields": [
    "region"
  ],
  "aggregations": [
    {
      "function": "count",
      "alias": "orders"
    },
    {
      "field": "amount",
      "function": "sum",
      "alias": "total_amount"
    },
    {
      "field": "amount",
      "function": "avg",
      "alias": "avg_amount"
    }
  ],
  "groupMatching": "normalized",
  "dateBucketGranularity": "month",
  "pivotFunction": "sum",
  "lenientNumbers": true,
  "sortDirection": "asc",
  "includeTotalsRow": false,
  "exportFormats": [
    "csv",
    "xlsx"
  ]
}
```

# Actor output Schema

## `aggregatedRows` (type: `string`):

One row per group (plus an optional totals row), with one column per aggregation and one column per pivot value.

## `csvFile` (type: `string`):

A ready-to-open CSV file of the aggregated result, if requested.

## `xlsxFile` (type: `string`):

A ready-to-open Excel (.xlsx) file of the aggregated result, if requested.

## `aggregateSummary` (type: `string`):

Row counts in and out, group count, the aggregations applied, skipped non-numeric values, and any warnings from this run.

# API

You can run this Actor programmatically using our API. Below are code examples in JavaScript, Python, and CLI, as well as the OpenAPI specification and MCP server setup.

## JavaScript example

```javascript
import { ApifyClient } from 'apify-client';

// Initialize the ApifyClient with your Apify API token
// Replace the '<YOUR_API_TOKEN>' with your token
const client = new ApifyClient({
    token: '<YOUR_API_TOKEN>',
});

// Prepare Actor input
const input = {
    "data": [
        {
            "orderId": 1001,
            "region": "North",
            "product": "Widget",
            "amount": "$1,200.00",
            "orderedAt": "2026-07-03"
        },
        {
            "orderId": 1002,
            "region": "North",
            "product": "Gadget",
            "amount": 350,
            "orderedAt": "2026-07-18"
        },
        {
            "orderId": 1003,
            "region": "South",
            "product": "Widget",
            "amount": "890.50",
            "orderedAt": "2026-07-22"
        },
        {
            "orderId": 1004,
            "region": "south",
            "product": "Gizmo",
            "amount": 120,
            "orderedAt": "2026-08-02"
        },
        {
            "orderId": 1005,
            "region": "East",
            "product": "Widget",
            "amount": 2400,
            "orderedAt": "2026-08-05"
        },
        {
            "orderId": 1006,
            "region": "East",
            "product": "Gadget",
            "amount": "n/a",
            "orderedAt": "2026-08-09"
        },
        {
            "orderId": 1007,
            "region": "North",
            "product": "Gizmo",
            "amount": 75,
            "orderedAt": "2026-08-11"
        },
        {
            "orderId": 1008,
            "region": "",
            "product": "Widget",
            "amount": 410,
            "orderedAt": "2026-08-14"
        }
    ],
    "groupByFields": [
        "region"
    ],
    "aggregations": [
        {
            "function": "count",
            "alias": "orders"
        },
        {
            "field": "amount",
            "function": "sum",
            "alias": "total_amount"
        },
        {
            "field": "amount",
            "function": "avg",
            "alias": "avg_amount"
        }
    ],
    "exportFormats": [
        "csv",
        "xlsx"
    ]
};

// Run the Actor and wait for it to finish
const run = await client.actor("nerolabs/dataset-aggregate-pivot").call(input);

// Fetch and print Actor results from the run's dataset (if any)
console.log('Results from dataset');
console.log(`💾 Check your data here: https://console.apify.com/storage/datasets/${run.defaultDatasetId}`);
const { items } = await client.dataset(run.defaultDatasetId).listItems();
items.forEach((item) => {
    console.dir(item);
});

// 📚 Want to learn more 📖? Go to → https://docs.apify.com/api/client/js/docs

```

## Python example

```python
from apify_client import ApifyClient

# Initialize the ApifyClient with your Apify API token
# Replace '<YOUR_API_TOKEN>' with your token.
client = ApifyClient("<YOUR_API_TOKEN>")

# Prepare the Actor input
run_input = {
    "data": [
        {
            "orderId": 1001,
            "region": "North",
            "product": "Widget",
            "amount": "$1,200.00",
            "orderedAt": "2026-07-03",
        },
        {
            "orderId": 1002,
            "region": "North",
            "product": "Gadget",
            "amount": 350,
            "orderedAt": "2026-07-18",
        },
        {
            "orderId": 1003,
            "region": "South",
            "product": "Widget",
            "amount": "890.50",
            "orderedAt": "2026-07-22",
        },
        {
            "orderId": 1004,
            "region": "south",
            "product": "Gizmo",
            "amount": 120,
            "orderedAt": "2026-08-02",
        },
        {
            "orderId": 1005,
            "region": "East",
            "product": "Widget",
            "amount": 2400,
            "orderedAt": "2026-08-05",
        },
        {
            "orderId": 1006,
            "region": "East",
            "product": "Gadget",
            "amount": "n/a",
            "orderedAt": "2026-08-09",
        },
        {
            "orderId": 1007,
            "region": "North",
            "product": "Gizmo",
            "amount": 75,
            "orderedAt": "2026-08-11",
        },
        {
            "orderId": 1008,
            "region": "",
            "product": "Widget",
            "amount": 410,
            "orderedAt": "2026-08-14",
        },
    ],
    "groupByFields": ["region"],
    "aggregations": [
        {
            "function": "count",
            "alias": "orders",
        },
        {
            "field": "amount",
            "function": "sum",
            "alias": "total_amount",
        },
        {
            "field": "amount",
            "function": "avg",
            "alias": "avg_amount",
        },
    ],
    "exportFormats": [
        "csv",
        "xlsx",
    ],
}

# Run the Actor and wait for it to finish
run = client.actor("nerolabs/dataset-aggregate-pivot").call(run_input=run_input)

# Fetch and print Actor results from the run's dataset (if there are any)
print(f"💾 Check your data here: https://console.apify.com/storage/datasets/{run.default_dataset_id}")
for item in client.dataset(run.default_dataset_id).iterate_items():
    print(item)

# 📚 Want to learn more 📖? Go to → https://docs.apify.com/api/client/python/docs/quick-start

```

## CLI example

```bash
echo '{
  "data": [
    {
      "orderId": 1001,
      "region": "North",
      "product": "Widget",
      "amount": "$1,200.00",
      "orderedAt": "2026-07-03"
    },
    {
      "orderId": 1002,
      "region": "North",
      "product": "Gadget",
      "amount": 350,
      "orderedAt": "2026-07-18"
    },
    {
      "orderId": 1003,
      "region": "South",
      "product": "Widget",
      "amount": "890.50",
      "orderedAt": "2026-07-22"
    },
    {
      "orderId": 1004,
      "region": "south",
      "product": "Gizmo",
      "amount": 120,
      "orderedAt": "2026-08-02"
    },
    {
      "orderId": 1005,
      "region": "East",
      "product": "Widget",
      "amount": 2400,
      "orderedAt": "2026-08-05"
    },
    {
      "orderId": 1006,
      "region": "East",
      "product": "Gadget",
      "amount": "n/a",
      "orderedAt": "2026-08-09"
    },
    {
      "orderId": 1007,
      "region": "North",
      "product": "Gizmo",
      "amount": 75,
      "orderedAt": "2026-08-11"
    },
    {
      "orderId": 1008,
      "region": "",
      "product": "Widget",
      "amount": 410,
      "orderedAt": "2026-08-14"
    }
  ],
  "groupByFields": [
    "region"
  ],
  "aggregations": [
    {
      "function": "count",
      "alias": "orders"
    },
    {
      "field": "amount",
      "function": "sum",
      "alias": "total_amount"
    },
    {
      "field": "amount",
      "function": "avg",
      "alias": "avg_amount"
    }
  ],
  "exportFormats": [
    "csv",
    "xlsx"
  ]
}' |
apify call nerolabs/dataset-aggregate-pivot --silent --output-dataset

```

## MCP server setup

```json
{
    "mcpServers": {
        "apify": {
            "type": "http",
            "url": "https://mcp.apify.com/?tools=fetch-actor-details,nerolabs/dataset-aggregate-pivot"
        }
    }
}

```

The hosted server signs you in with OAuth on first connect, so no API token belongs in this config. Clients without OAuth support can send an `Authorization: Bearer <APIFY_API_TOKEN>` header instead, using a token from API & Integrations in Apify Console (https://console.apify.com/settings/integrations).

## OpenAPI specification

Download the OpenAPI definition: https://api.apify.com/v2/actors/pWvRFHzxJdAIIZSKe/builds/RL3BwNG9yATq1WpGT/openapi.json