Merge, Deduplicate & Join Datasets

Merge up to 10 of your Apify datasets into one, remove duplicates by the fields you choose (keep the first or the last), and join extra fields from a lookup dataset, like VLOOKUP in a spreadsheet. Every output row keeps your own fields at the top level, so the next step (a spreadsheet, a database, a webhook) gets flat rows. It runs with limited permissions: it reads only the datasets you choose.

Typical uses:

• Combine several runs of a scraper (or several scrapers) into one clean dataset without duplicate URLs or IDs.
• Keep the newest version of every item: put the older dataset first and the newer one last, and choose "keep last".
• Enrich scraped items with your own reference data: categories, supplier contacts, internal SKUs.
• Match keys that are written differently: extra spaces, upper/lower case, and for Japanese data full-width/half-width characters (ＡＢ１ = AB1, ｶﾅ = カナ) and katakana/hiragana (カナ = かな).

Try it in one click

Click Start without choosing a dataset. The Actor merges two small built-in sample datasets of 30 made-up products (16 + 14 items) by sku and joins a list of 6 makers by maker:

• With the default key matching (spaces only), 3 duplicates are removed (AB-103 twice in shop A, AB-109 with spaces, AB-102 in both shops) and 27 rows are written; ＡＢ－１００ and ab-101 stay separate from AB-100 and AB-101. 13 rows find their maker; 14 do not: 12 because the maker's name is written in another form (さくら工房 / サクラ工房, half-width ｱｵﾊﾞ電機, LUMEN GOODS), 2 because the maker is not in the list.
• Run it again with all four Key matching options ticked: 7 duplicates are removed, 23 rows are written, and 21 of them get the maker's country, support email and lead time. The 2 rows left have a maker that is not in the list.
• Every row shows the dataset it came from (_sourceDataset) and how many duplicates were removed for it (_duplicateCount).

The sample uses fixed field names; Key matching, Which duplicate to keep and Items without a match from your input apply. It is not charged per item (only Apify's Actor start event applies, see Pricing).

How it works

1. You choose the datasets to merge: Dataset (datasetId) and, optionally, More datasets to merge (datasetIds), with the picker or by ID. They are read in this order.

2. Duplicates (on by default): items with the same value in the duplicate key fields (dedupeKeys, for example url or sku) are duplicates. Without key fields, two items are duplicates only when all their fields are equal (or all the Fields to keep, if you set them), text values compared with the key matching options. Duplicates are found before the join, so lookup columns in Fields to keep are not part of this comparison: two items that differ only in what the lookup adds are still duplicates. Items without a value in the key fields are always kept.

3. Which duplicate is kept depends only on the order of the datasets. "First" keeps the item from the dataset read first, "Last" the item from the dataset read last; the kept item stays at its own position. To keep the newest version with "Last", put the newest data last. In an integration, where the new run is usually in Dataset (read first), "First" keeps the new run.

4. Join (optional): if you choose a lookup dataset, every kept item looks up its joinKey value in the lookup dataset's lookupKey field and gets that lookup item's fields, with a prefix (lookup_ by default) so that nothing of your item is replaced. If several lookup items have the same key, the first one is used. Items without a match are kept (left join) or dropped (inner join). Duplicates are removed before the join: with "keep last" and an inner join, a key whose last item has no match gives no row, even if an earlier item of that key would have matched.

5. Key matching (keyNormalization) decides how loosely key values are compared, for duplicates and for the join. The output always keeps the original values.

6. Output fields (optional), applied after the join: the columns to write and their order (fields, which can include lookup columns such as lookup_category), then new names (renameFields). _sourceDataset and _duplicateCount are added last, after the renames.

7. Before anything is charged, the Actor checks that every dataset exists and is not chosen twice, looks at the first items of each dataset (100, or fewer when the items are big), and plans the memory of the run. It stops with a message that says what to change, and charges nothing, if:

   • none of the first items has a value in the duplicate key fields or the join key, or none of the first lookup items has a value in the lookup key (likely a typo; the message lists the fields it found);
   • the lookup dataset is empty and the join is an inner join (it would write nothing);
   • "keep last" or the duplicate count is on and your maximum cost per run does not cover all items (these options read everything before the first row is written; the message says what maximum to set);
   • the run does not have enough memory (the message says how much to choose);
   • the dataset was passed only in an integration's payload (see "Run it after your scraper").

   When only some of the first items have a key value, or none of them finds a lookup item, the log shows a warning and the run continues: an optional key field, or an inner join with a short watch list, are normal uses.

8. The Actor writes one dataset row per output item and a summary with the counts per dataset to the OUTPUT record of its key-value store.

Key matching options:

Option	Matches
trim (default)	Spaces at both ends are ignored and runs of white space count as one space: AB 100 = AB 100. Line breaks, tabs, no-break spaces and the ideographic space count as white space too, so without dedupe key fields, two descriptions that differ only in line breaks are the same.
case	Upper and lower case are the same: ABC = abc, STRASSE = Straße.
width	Full-width and half-width forms are the same: ＡＢ－１００ = AB-100, ｶﾞ = ガ, the ideographic space = a space. Kana followed by a separate voiced sound mark (カ゛) also matches the combined character (ガ). Canonically equivalent characters are unified too (Unicode NFC: é written as e + accent, CJK compatibility ideographs, the Ohm sign = Ω). Other compatibility characters stay as they are: circled numbers, ligatures, superscripts and square units are not folded.
kana	Katakana and hiragana are the same: サクラ = さくら = ｻｸﾗ.

Input

Field	What it does
datasetId	The first (or only) dataset to merge, by ID or with the picker. In an integration, set it to {{resource.defaultDatasetId}}. Leave it and datasetIds empty to try the sample.
datasetIds	More datasets to append, in order (at most 10 datasets in all). Put the newest last if you want "keep last" to keep the newest. {{resource.defaultDatasetId}} also works in this list.
removeDuplicates	true (default) removes duplicates; false just appends the datasets.
dedupeKeys	Fields that identify the same item, for example ["url"] or ["productId", "size"]. Dots for nested fields: product.id. Empty = whole items (or the fields to keep) are compared.
keep	first (default) or last: the item from the dataset read first, or read last.
lookupDatasetId	Optional lookup dataset for the join, by ID or with the picker.
joinKey	The field of your items to look up, for example sku. Required with a lookup dataset.
lookupKey	The matching field of the lookup dataset. Empty = the same name as joinKey.
joinType	left (default, keep items without a match) or inner (drop them).
lookupFields	Lookup fields to add. Empty = all fields of the lookup item. With a list, items without a match get these fields as null.
lookupPrefix	Put before the names of the added fields. Default lookup_; an empty string in the JSON input adds them without a prefix.
keyNormalization	Any of trim, case, width, kana (default ["trim"]). [] compares values exactly.
fields	The output columns, in this order: your fields and lookup columns (with their prefix). A nested field (offer.price) becomes a column of that name. Missing values are written as null.
renameFields	New names for output columns: {"title": "name", "lookup_category": "category"}.
addSourceDataset	Adds _sourceDataset (the ID of the dataset the item came from). Default false.
addDuplicateCount	Adds _duplicateCount (how many other items with the same key were removed). Default false.

Datasets must be chosen with the picker or given by ID: a dataset name is not found, because the Actor runs with limited permissions.

Example input: merge two runs of a product scraper, keep the newest version of each URL (the newer run is last), and add the category from your own product list.

{
    "datasetId": "YOUR_OLDER_DATASET_ID",
    "datasetIds": ["YOUR_NEWER_DATASET_ID"],
    "dedupeKeys": ["url"],
    "keep": "last",
    "lookupDatasetId": "YOUR_PRODUCT_LIST_DATASET_ID",
    "joinKey": "sku",
    "lookupKey": "productCode",
    "lookupFields": ["category", "supplier"],
    "keyNormalization": ["trim", "case"],
    "addSourceDataset": true
}

Output

Each dataset row is one output item: your item's fields as they were, then the added lookup fields, then the optional helper fields. For the example input above:

{
    "url": "https://example.com/products/42",
    "sku": "ab-42",
    "name": "Desk lamp",
    "price": 34.9,
    "stock": { "status": "in_stock", "quantity": 12 },
    "lookup_category": "Lighting",
    "lookup_supplier": "Example Supplies Ltd.",
    "_sourceDataset": "YOUR_NEWER_DATASET_ID"
}

Nested fields stay nested (stock above). The dataset's overview view returns all fields (checked through the API with ?view=overview). Hidden fields (names starting with #) are not copied.

The OUTPUT record of the run's key-value store holds the summary. It says "status": "RUNNING" while the run works (it is updated after every page), then COMPLETE, PARTIAL or FAILED. The counts are given in total and per dataset. For the built-in sample with all four key matching options:

{
    "status": "COMPLETE",
    "message": "Read 30 items from 2 datasets: 7 duplicates removed, 23 items written. Join: 21 of 23 items found a lookup item; 2 without a match were kept. (Built-in sample data. Choose your datasets to merge your own data.)",
    "mode": "sample",
    "datasetIds": ["sample-shop-a", "sample-shop-b"],
    "lookupDatasetId": "sample-makers",
    "counts": {
        "itemsRead": 30, "itemsWritten": 23, "duplicatesRemoved": 7, "itemsWithoutKey": 0,
        "joinMatched": 21, "joinUnmatched": 2, "joinWithoutKey": 0, "droppedUnmatched": 0, "notObjects": 0,
        "datasets": [
            { "datasetId": "sample-shop-a", "itemsRead": 16, "duplicatesRemoved": 2, "itemsWritten": 14, "joinMatched": 13, "joinUnmatched": 1, "...": "..." },
            { "datasetId": "sample-shop-b", "itemsRead": 14, "duplicatesRemoved": 5, "itemsWritten": 9, "joinMatched": 8, "joinUnmatched": 1, "...": "..." }
        ],
        "lookup": { "datasetId": "sample-makers", "itemsRead": 6, "itemsWithoutKey": 0, "duplicateKeys": 1, "keys": 5, "chargedAs": 0 },
        "fieldClashes": 0, "renameClashes": 0, "helperOverwrites": 0, "unsafeIntegerKeys": 0
    },
    "itemsWritten": 23,
    "itemsAddedAfterStart": [],
    "chargedItems": 0
}

• duplicatesRemoved is counted in the dataset the removed item came from.
• joinUnmatched includes the items without a join key value (also counted in joinWithoutKey); droppedUnmatched is how many of them an inner join dropped.
• fieldClashes: lookup values not added because your item already had a field of that name. renameClashes: fields replaced by a renamed field of the same new name. helperOverwrites: your own _sourceDataset or _duplicateCount fields replaced by the added ones.
• lookup.chargedAs: the item-processed events charged for the lookup dataset (1 per started 20 lookup items; 0 for the sample, which is not charged).
• itemsAddedAfterStart: datasets that got more items while the run was going (see Limits).

Run it after your scraper

1. Open your scraper (or its saved task) in Apify Console and go to its Integrations tab.
2. Add this Actor as an integration that runs when the scraper's run succeeds.
3. In the integration's input, set for example:

{
    "datasetId": "{{resource.defaultDatasetId}}",
    "dedupeKeys": ["url"],
    "lookupDatasetId": "YOUR_PRODUCT_LIST_DATASET_ID",
    "joinKey": "sku"
}

{{resource.defaultDatasetId}} is replaced with the dataset of the scraper run that just finished. Then add your next step (for example a spreadsheet or notification integration) on this Actor's own Integrations tab, so it receives the clean rows.

• Keep datasetId (or datasetIds) in the integration's input. The integration also passes the scraper run in a payload field, but this Actor runs with limited permissions and cannot read a dataset from there. Without it, the run stops before charging and asks you to add it.
• To merge the new run with older datasets and keep the newest version of each item, put the new run last and use "keep last": "datasetIds": ["YOUR_OLDER_DATASET_ID", "{{resource.defaultDatasetId}}"], "keep": "last". With the new run in datasetId (read first), use "keep first".

Pricing

This Actor uses pay-per-event pricing. The current prices are on the Pricing tab of this Actor.

Event	Price	When it is charged
Actor start	$0.00005 per run (once more for each GB above 1 GB)	Set by Apify, also for the sample data: once for runs with up to 1 GB of memory, plus once for each extra GB (a 4 GB run is charged 4 times). The default memory of this Actor is 1 GB.
Item processed (item-processed)	$0.0001 per item ($0.10 per 1,000 items)	Once for every item read from the datasets to merge, including removed duplicates and items an inner join drops, and once for every started 20 items of the lookup dataset (charged with the first page). Each page is charged for the larger of the items read and the items written; one item never gives more than one row, so this is the number of items read.

• The cost of a run is predictable: it grows with the size of your datasets, not with the number of duplicates or matches. Examples: merging two datasets of 5,000 items each with a lookup of 500 items is 10,000 + 25 = 10,025 events, about $1.00. Joining 50 new items with a lookup of 200,000 items is 50 + 10,000 = 10,050 events, $1.005.
• keep: "last" and the duplicate count read the datasets twice; each item is still charged once. Because they read everything before writing the first row, they need a maximum cost per run that covers all items; otherwise the run stops at the start and charges nothing.
• Runs that stop at the checks before processing (input problem, no key value in any of the first items, an inner join with an empty lookup dataset, a maximum cost too low for "keep last", not enough memory) are not charged per item.
• Maximum cost per run: if you set one, the Actor counts how many items it can afford before each page and stops cleanly when the limit is reached. The rows written so far are kept and the summary says "status": "PARTIAL"; it is saved before the charge that reaches your limit, because Apify stops a run as soon as its limit is reached. The lookup charge counts against the limit: if the limit does not cover it plus one item, nothing is read or charged. The log tells you at the start if your limit does not cover all items. Removed duplicates and items an inner join drops are charged too, so a partial run can have charged items that wrote no row.
• If a run is interrupted (for example moved to another server by Apify), it continues from the last saved page; an item is never charged twice.

Limits

Measured on Apify (1024 MB memory, build 0.1.1):

• 10,000 items read and written took 7.5 seconds; the run used at most 132 MB of memory. One read pass ran at about 14,000 items per second (twice that many reads for "keep last" or the duplicate count).
• The platform cost of a run (our cost, not your price) was about $0.0055 per 1,000 items written.

Measured locally (Node.js 24, Windows):

• The merge itself, without reading through the API: two datasets of 100,000 items each (items of about 300 bytes, half of them duplicates by url) take 0.45 seconds with "keep first" and 1.6 seconds with "keep last", duplicate counts, all four key normalizations and a join with 500 lookup items.
• The duplicate keys take 16 to 32 MB of memory per 1,000,000 items with "keep first", 32 to 64 MB with "keep last" or the duplicate count (64-bit hashes outside the JavaScript heap, in a table sized in powers of two), not the items themselves.
• The lookup dataset is held in memory: about 390 bytes per lookup item whose JSON has about 135 characters, mostly Japanese (Japanese text takes 2 bytes per character in memory), and about 270 bytes for 170 characters of English text; less with lookupFields.
• Big items: pages hold about 4 MB of JSON (13 items of 300 KB, 1 item of several MB). With the heap of a 256 MB run, 40 items of 300 KB used at most 126 to 147 MB, and 6 items of 3 MB at most 138 MB. 256 MB is the minimum memory of this Actor.

Estimates (not measured on Apify): the memory check before charging accepts a lookup of about 135,000 items with 400 bytes of JSON text in memory each (about 400 characters of English or 200 of Japanese) in a run with 256 MB, 320,000 with 512 MB, 700,000 with the default 1024 MB and 1,400,000 with 2048 MB. Above that, it stops before charging and tells you how much memory to choose. The datasets to merge need little memory whatever their number of items; their items' size only matters for one page at a time. One case the check cannot foresee: items that suddenly become much bigger far into a dataset; the run can then fail on that page (the pages before it are kept and charged).

Partial runs (maximum cost, timeout, abort). A run can end with "status": "PARTIAL" in OUTPUT:

• when it reaches your maximum cost per run (see Pricing);
• 30 seconds before the run's timeout: the Actor stops itself so that it can still save the rows and the summary (if every item was already processed, it finishes as COMPLETE);
• when you abort the run (same exception);
• rarely, when Apify moves a run to another server and the restarted run no longer fits in its memory: the rows written before the move are kept and charged, and the run fails with a message that suggests more memory.

At the maximum cost and at the timeout, the run itself ends as succeeded (an aborted run is shown as aborted), so an integration that starts on "run succeeded" also starts after a partial run. Check status in OUTPUT if your next step needs the full result. A partial run has read the datasets only up to the point where it stopped. Rarely, when Apify interrupts a run in the middle of saving a page, the rows of that page are written twice after the restart (they are charged once).

Matching.

• At most 10 datasets per run, and one lookup dataset. Each dataset is read up to the number of items it had when the run started. Right after the last push of a scraper, the item count that the API reports can be behind; the Actor counts the items after it at the start (the log says so) and reads them too. Items added while the run is going are not read: the log and OUTPUT (itemsAddedAfterStart) say how many; run it again to include them.
• Key values are compared as text, so 123 and "123" are the same key. A key value that is an object or a list is compared by its JSON text (a list as a whole, in order), so {"a":1} and the text '{"a":1}' are the same key. Integer IDs above 2^53 cannot be kept exactly by JSON numbers, so different IDs may look the same; they are counted in unsafeIntegerKeys. Use a text field as the key if you can.
• Keys are compared by a 64-bit hash. Two different keys could in theory get the same hash (with 10 million keys the chance is about 1 in 400,000); one item would then be removed as a duplicate of the other, or get the other key's lookup fields.
• A field set to null and a missing field both count as "no value" for keys.
• The join uses the first lookup item of each key and gives at most one row per item (no row multiplication). Duplicate lookup keys are counted in OUTPUT (lookup.duplicateKeys).
• A field literally named a.b and a nested field a → b have the same dotted path; avoid keys that depend on telling them apart.
• Items must be JSON objects; anything else is skipped and counted (notObjects).
• The Actor only reads the datasets you choose in the input (it runs with limited permissions).

Privacy

• The Actor does not keep your data. It reads the datasets you choose, writes the rows to its own run dataset in your account, and stops.
• During the run, keys are held in memory as 64-bit hashes (the first 8 bytes of an unsalted SHA-256) and the lookup dataset is held in memory; nothing of it is stored after the run. The run's key-value store keeps only the summary (OUTPUT) and a progress record with counts and settings.
• When none of the first items finds a lookup item, the warning in the run's log shows up to three key values from each side, so that you can see why they differ.
• The Actor does not contact any website or service outside your Apify account.

More from Quiet Works Lab

• Dataset Change Tracker: compare two runs of your scraper and get only the new, changed and removed items.
• Image Converter & Compressor: convert images to WebP, AVIF, JPEG or PNG, resize them and remove EXIF and GPS metadata.
• Japanese Text Normalizer & RAG Chunker: clean Japanese text before an LLM or RAG and split it into sentence-aware chunks.

Example tasks

Ready-made inputs for common jobs. Open one, then click Try for free to copy it to your account and choose your datasets:

• Merge datasets and remove duplicates by a key field
• Join two datasets by a key field, like VLOOKUP
• Keep the latest version of each item when merging scraper runs
• 全角半角・カナかなの違いを吸収してデータセットを統合・重複除去

More from Quiet Works Lab

• Dataset Change Tracker: compare two runs of your scraper and get only the new, changed and removed items.
• Japanese Text Normalizer & RAG Chunker: clean Japanese text before an LLM or RAG and split it into sentence-aware chunks.
• Image Converter & Compressor: convert images to WebP, AVIF, JPEG or PNG, resize them and remove EXIF and GPS metadata.

Support

This Actor is developed and maintained with the help of AI (Claude Code). Issues are answered within a few days.

Please open an issue on the Issues tab with the run ID and, if possible, a few example items (remove anything private first).

日本語の概要

自分の Apify データセットを最大10個まとめ、指定した項目（url・sku など）で重複を除き、別のデータセット（商品マスタなど）から VLOOKUP のように項目を足します。どちらの重複を残すかは並び順だけで決まります（first = 先に読んだもの、last = 後に読んだもの）。最新の版を残したいときは、新しいデータを最後に置いて last を選んでください。キーの比べ方は、前後の空白・大文字小文字・全角半角（ＡＢ１ = AB1、ｶﾅ = カナ）・カタカナとひらがな（カナ = かな）を選んで無視できます。比べるときだけ揃え、出力の値はそのままです。料金は読んだ件数で1件 $0.0001、参照データセットは20件ごとに1件分です。データセットは選択の欄か ID で指定します（名前では見つかりません）。データセットを選ばずに Start すると、同梱の見本（架空の商品30件）で結果を確かめられます。