DEV Community

Aurelix Solutions
Aurelix Solutions

Posted on

Merge Apify datasets and CSV files without duplicates (www, https and utm tags included)

#python #datascience #automation #webscraping

Every scraper user hits this: you ran the same search for five cities, or the same scraper every day for a week, and now you have five datasets with the same businesses in several of them. Worse, the duplicates are not exact. One run has https://www.acme.com/, another has acme.com?utm_source=newsletter; one row says INFO@Acme.com, another info@acme.com. A plain "remove duplicates" treats all of those as different.

This post merges sources like that in one call, then sets up a daily "only new rows" step after a scheduled scraper.

Disclosure: I'm Aurelix, and I built Merge CSV Files & Datasets. It's a paid tool (prices below).

What goes in, what comes out

Sources, in any mix: Apify datasets (by ID, or picked in the Console), public links to CSV, TSV, JSON, JSON Lines or Excel files, Google Sheets shared "anyone with the link", and rows pasted as JSON.

Out: one dataset with the unique rows, each tagged with _source, and a STATS record with rows read, written and removed per source, plus any source that could not be read and why. A wrong dataset ID or a dead link is named there and in the run's status; the run itself still finishes and returns what it could read.

One list from several runs 

pip install apify-client
Enter fullscreen mode Exit fullscreen mode 
import os

from apify_client import ApifyClient

client = ApifyClient(os.environ["APIFY_TOKEN"])
run = client.actor("aurelix/dataset-merge-dedup").call(
    run_input={
        "datasetIds": ["<run 1 dataset ID>", "<run 2 dataset ID>", "<run 3 dataset ID>"],
        "fileUrls": ["https://example.com/old-leads.csv"],
        "dedupeFields": ["website"],
        "keep": "last",
        "outputFields": ["name", "website", "phone", "city"],
    }
)
rows = list(client.dataset(run["defaultDatasetId"]).iterate_items())
stats = client.key_value_store(run["defaultKeyValueStoreId"]).get_record("STATS")["value"]
print(f"{stats['rowsWritten']} unique of {stats['rowsRead']} read, {stats['duplicatesRemoved']} duplicates removed")
for source, s in stats["sources"].items():
    print(f"  {source[:60]}: read {s['read']}, written {s['written']}" + (f", NOT READ: {s['error']}" if s["error"] else ""))
Enter fullscreen mode Exit fullscreen mode

keep: "last" keeps the copy from the latest source, so list the datasets oldest first and the newest data wins. With smart matching on (the default) the website comparison ignores the scheme, www., the host's case, one trailing slash and utm_*, gclid and fbclid tags (other parameters and #fragments still count), and emails are compared in any case. Phone numbers are not reformatted, so dedupe by website or email when phones are written differently. Rows that have no website at all are kept and counted separately rather than collapsed into one row, which is the usual cause of "my dedup returned almost nothing".

Only new rows after a daily scraper 

run_input = {
    "datasetIds": ["{{resource.defaultDatasetId}}"],
    "dedupeFields": ["website"],
    "onlyNewEntries": True,
    "stateKey": "daily-leads",
}
Enter fullscreen mode Exit fullscreen mode

Add the Actor as an integration on your scraper's task with that input: each time the scraper finishes, this run reads its dataset and passes on only rows whose website no earlier run returned. Point Apify's Google Sheets or Slack integration at this run instead of the scraper and you only ever see new leads.

Why this tool

  • Merges Apify datasets and files (CSV, JSON, Excel, Google Sheets) in one run.
  • URL and email matching that ignores the noise (scheme, www., tracking tags).
  • Only rows new since the last run, for a daily scraper.
  • $0.02 per 1,000 rows written ($0.015 on paid plans), plus $0.0015 per 1,000 rows and $0.00005 per MB read, and $0.005 per run.
  • What it doesn't cover: filters, lookup joins or JavaScript transforms.

Limits worth knowing

  • About 5 million unique rows per run fit in the default 1 GB (2 million with keep last); at that point the run stops cleanly and says so. Excel files up to 30 MB with up to 40 MB of text; CSV, JSON Lines and JSON lists up to 200 MB, all streamed.
  • A dataset from another Apify account is readable only if that account shares it by link.
  • No transforms beyond choosing and flattening columns with outputFields.

Top comments (0)